Calculate Conversion Rate from Ad Clicks to Page Visits
TikTokData Scientist · Technical Screen
Last updated
This question evaluates a candidate's competency in event-level data manipulation and metric computation using SQL or Python, including time-based aggregation, deduplication, and joining or windowing of event streams.
user_events
| user_id | event_time | event_type | ad_id |
|---|---|---|---|
| 1 | 2023-01-01 10:00:00 | ad_click | 101 |
| 1 | 2023-01-01 10:05:00 | page_visit | NULL |
| 2 | 2023-01-01 11:00:00 | ad_click | 102 |
| 3 | 2023-01-01 11:30:00 | page_visit | NULL |
| 2 | 2023-01-01 11:15:00 | page_visit | NULL |
Scenario
An online advertising platform wants to measure how well ad clicks convert into subsequent page visits.
Question
Given the user_events table, write SQL that computes the overall conversion rate defined as the share of ad_click events that are followed by at least one page_visit by the same user on the same calendar day. Clearly state the numerator and denominator you use in the metric definition.
Hints
Use distinct user_id and event_type, date truncation, and a left join or window functions to link clicks to visits.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.