Coding questions · TikTok
Calculate Conversion Rate from Ad Clicks to Page Visits
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.