Click-Through Rate per App for One Calendar Year
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_typeis either'impression'(an ad was shown) or'click'(an ad was tapped).tsis 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.