Calculate Conversion Rate from Ad Clicks to Page Visits
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
Given the user_events table, compute 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.
Tables
user_events(user_id INTEGER, event_time TIMESTAMP, event_type VARCHAR(50), ad_id INTEGER)
Hints
- Truncate or cast timestamps to date to enforce same-calendar-day comparisons (e.g., CAST(event_time AS DATE) or DATE_TRUNC to day).
- For each ad_click, check if an equal user_id has any page_visit later on the same day (EXISTS or LEFT JOIN).