Click-Through Rate per App for One Calendar Year

EasySQL

Reported in interviews at Meta

Problem statement

An ad analytics pipeline logs one row per ad event shown inside partner mobile apps:

events(app_id INT, event_type TEXT, ts TEXT)

  • event_type is either 'impression' (an ad was shown) or 'click' (an ad was tapped).
  • ts is the event time as ISO text, 'YYYY-MM-DD HH:MM:SS'.

Write a query that returns the click-through rate of each app for calendar year 2019, where

$$\text{CTR} = \frac{\text{clicks in 2019}}{\text{impressions in 2019}}.$$

Output: columns app_id, ctr, one row per app that has at least one event in 2019, ordered by app_id ascending. Report ctr as a decimal fraction (0.25, not 25%); any rounding to 4 or more decimals is fine. If an app has 2019 events but no 2019 impressions, its ctr is NULL. Events outside 2019 are ignored. Use the SQLite dialect.

Example

app_id | event_type | ts
101    | impression | 2019-03-01 09:00:00
101    | impression | 2019-03-01 09:05:00
101    | click      | 2019-03-01 09:05:10
101    | impression | 2019-07-20 18:40:00
101    | impression | 2018-12-31 23:59:00
205    | impression | 2019-11-02 14:00:00
205    | impression | 2019-11-02 14:10:00
205    | click      | 2019-11-02 14:10:30
205    | click      | 2020-01-01 00:00:05
318    | click      | 2019-05-05 05:05:05
404    | impression | 2020-02-02 10:00:00

Expected output:

app_id | ctr
101    | 0.3333
205    | 0.5
318    | NULL

App 101 has 3 impressions and 1 click in 2019 (the 2018 impression is excluded). App 205's 2020 click is excluded. App 318 has a 2019 click but no 2019 impression. App 404 has no 2019 events, so it does not appear.

Show hint

How can a single grouped scan produce two different counts from one event-type column?

Relevant roles: Data Scientist and Forward Deployed Engineer.

Related free problems