Determine Top Advertisers by Conversion Rate and CTR Analysis
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
ads
+-------+---------------+------------+
| ad_id | advertiser_id | created_at |
+-------+---------------+------------+
| 1 | 101 | 2023-07-01 |
| 2 | 102 | 2023-07-02 |
| 3 | 101 | 2023-07-03 |
+-------+---------------+------------+
ads_impressions
+------------+-------+---------------------+
| impression_id | ad_id | timestamp |
+------------+-------+---------------------+
| 9001 | 1 | 2023-07-10 18:05 |
| 9002 | 2 | 2023-07-10 14:22 |
| 9003 | 1 | 2023-07-11 19:10 |
+------------+-------+---------------------+
ads_conversions
+--------------+------------+---------------------+
| conversion_id | impression_id | timestamp |
+--------------+------------+---------------------+
| 5001 | 9001 | 2023-07-10 18:06 |
| 5002 | 9003 | 2023-07-11 19:11 |
+--------------+------------+---------------------+
##### Scenario
Three tables – ads, ads_impressions, ads_conversions – are provided to evaluate ad performance.
##### Question
Write a query to compute overall click-through-rate (CTR) per ad for the last 7 days. Return the top 3 advertisers by conversion rate in the most recent week. For each hour of day, compare CTR at peak hours (18:00-22:
00) versus non-peak; output the p-value for difference in proportions. Suggest additional data or tests to confirm: "Ads exposed at peak hours perform better than at non-peak hours."
##### Hints
Think joins, filtering by timestamps, aggregation, proportion tests.
Overview: This question evaluates a candidate's competency in SQL/Python data manipulation and applied statistical analysis for advertising metrics, specifically computing click-through rates, conversion rates, time-based aggregations, and performing proportion tests for peak versus non-peak hours.
CTR per ad, last 7d
Compute conversion-through rate per ad over the most recent 7-day window anchored to the latest impression timestamp. Treat an impression as converted if it has at least one row in `ads_conversions`; `ctr_7d` is converted impressions divided by total impressions for the ad. Return `ad_id`, `advertiser_id`, `impressions_7d`, `conversions_7d`, and `ctr_7d` rounded to 4 decimals, ordered by `ad_id`.
Tables
ads(ad_id INTEGER, advertiser_id INTEGER, created_at DATE)
ads_impressions(impression_id INTEGER, ad_id INTEGER, timestamp TIMESTAMP)
ads_conversions(conversion_id INTEGER, impression_id INTEGER, timestamp TIMESTAMP)
Hints
- Anchor the 7-day window to MAX(ads_impressions.timestamp).
- Treat any impression with at least one conversion as converted once.
Top advertisers by CVR
List the top 3 advertisers by conversion rate over the most recent 7-day window anchored to the latest impression timestamp. Conversion rate is conversions divided by impressions after mapping impressions to advertisers through `ads`. Break ties by conversions descending, impressions descending, then advertiser_id ascending. Return `advertiser_id`, `impressions_7d`, `conversions_7d`, `conversion_rate_7d`, and `rank`.
Tables
ads(ad_id INTEGER, advertiser_id INTEGER, created_at DATE)
ads_impressions(impression_id INTEGER, ad_id INTEGER, timestamp TIMESTAMP)
ads_conversions(conversion_id INTEGER, impression_id INTEGER, timestamp TIMESTAMP)
Hints
- Aggregate at advertiser level after mapping impressions to ads.
- Use ROW_NUMBER with ordered tie-breakers.
Hourly peak vs non-peak CTR
For each hour-of-day in the most recent 7-day window anchored to the latest impression timestamp, compute the hour's CTR and compare it to the opposite group (peak hours 18–22 vs non-peak aggregate) using a two-proportion z-test. Return hour_of_day, is_peak, impressions_hour, conversions_hour, ctr_hour, comparison_group, comp_impressions, comp_conversions, and the two-tailed p_value.
Tables
ads(ad_id INTEGER, advertiser_id INTEGER, created_at DATE)
ads_impressions(impression_id INTEGER, ad_id INTEGER, timestamp TIMESTAMP)
ads_conversions(conversion_id INTEGER, impression_id INTEGER, timestamp TIMESTAMP)
Hints
- Compute per-hour tallies, then aggregate totals for peak and non-peak groups.
- Use a pooled two-proportion z-test: z = (p1 - p2) / sqrt(p*(1-p)*(1/n1 + 1/n2)).