Coding questions · Meta
Determine Top Advertisers by Conversion Rate and CTR Analysis
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.
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.
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.
Table ads
| column | type | constraints |
|---|---|---|
| ad_id | INTEGER | PRIMARY KEY, NOT NULL |
| advertiser_id | INTEGER | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| ad_id | advertiser_id | created_at |
|---|---|---|
| 1 | 101 | 2023-07-01 |
| 2 | 102 | 2023-07-02 |
| 3 | 101 | 2023-07-03 |
Table ads_impressions
| column | type | constraints |
|---|---|---|
| impression_id | INTEGER | PRIMARY KEY, NOT NULL |
| ad_id | INTEGER | NOT NULL, FOREIGN KEY -> ads.ad_id |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| ad_id | impression_id | timestamp |
|---|---|---|
| 1 | 9001 | 2023-07-10 18:05 |
| 2 | 9002 | 2023-07-10 14:22 |
| 1 | 9003 | 2023-07-11 19:10 |
Table ads_conversions
| column | type | constraints |
|---|---|---|
| conversion_id | INTEGER | PRIMARY KEY, NOT NULL |
| impression_id | INTEGER | NOT NULL, FOREIGN KEY -> ads_impressions.impression_id |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| conversion_id | impression_id | timestamp |
|---|---|---|
| 5001 | 9001 | 2023-07-10 18:06 |
| 5002 | 9003 | 2023-07-11 19:11 |
Expected output
CTR per ad over the last 7 days anchored to the latest impression timestamp. CTR = conversions_7d / impressions_7d.
| ad_id | advertiser_id | conversions_7d | ctr_7d | impressions_7d |
|---|---|---|---|---|
| 1 | 101 | 2 | 1 | 2 |
| 2 | 102 | 0 | 0 | 1 |
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.
Table ads
| column | type | constraints |
|---|---|---|
| ad_id | INTEGER | PRIMARY KEY, NOT NULL |
| advertiser_id | INTEGER | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| ad_id | advertiser_id | created_at |
|---|---|---|
| 1 | 101 | 2023-07-01 |
| 2 | 102 | 2023-07-02 |
| 3 | 101 | 2023-07-03 |
Table ads_impressions
| column | type | constraints |
|---|---|---|
| impression_id | INTEGER | PRIMARY KEY, NOT NULL |
| ad_id | INTEGER | NOT NULL, FOREIGN KEY -> ads.ad_id |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| ad_id | impression_id | timestamp |
|---|---|---|
| 1 | 9001 | 2023-07-10 18:05 |
| 2 | 9002 | 2023-07-10 14:22 |
| 1 | 9003 | 2023-07-11 19:10 |
Table ads_conversions
| column | type | constraints |
|---|---|---|
| conversion_id | INTEGER | PRIMARY KEY, NOT NULL |
| impression_id | INTEGER | NOT NULL, FOREIGN KEY -> ads_impressions.impression_id |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| conversion_id | impression_id | timestamp |
|---|---|---|
| 5001 | 9001 | 2023-07-10 18:06 |
| 5002 | 9003 | 2023-07-11 19:11 |
Expected output
Top 3 advertisers by conversion rate over the last 7 days anchored to the latest impression timestamp, with ties broken by conversions then impressions.
| advertiser_id | conversion_rate_7d | conversions_7d | impressions_7d | rank |
|---|---|---|---|---|
| 101 | 1 | 2 | 2 | 1 |
| 102 | 0 | 0 | 1 | 2 |
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.
Table ads
| column | type | constraints |
|---|---|---|
| ad_id | INTEGER | PRIMARY KEY, NOT NULL |
| advertiser_id | INTEGER | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| ad_id | advertiser_id | created_at |
|---|---|---|
| 1 | 101 | 2023-07-01 |
| 2 | 102 | 2023-07-02 |
| 3 | 101 | 2023-07-03 |
Table ads_impressions
| column | type | constraints |
|---|---|---|
| impression_id | INTEGER | PRIMARY KEY, NOT NULL |
| ad_id | INTEGER | NOT NULL, FOREIGN KEY -> ads.ad_id |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| ad_id | impression_id | timestamp |
|---|---|---|
| 1 | 9001 | 2023-07-10 18:05 |
| 2 | 9002 | 2023-07-10 14:22 |
| 1 | 9003 | 2023-07-11 19:10 |
Table ads_conversions
| column | type | constraints |
|---|---|---|
| conversion_id | INTEGER | PRIMARY KEY, NOT NULL |
| impression_id | INTEGER | NOT NULL, FOREIGN KEY -> ads_impressions.impression_id |
| timestamp | TIMESTAMP | NOT NULL |
Sample data:
| conversion_id | impression_id | timestamp |
|---|---|---|
| 5001 | 9001 | 2023-07-10 18:06 |
| 5002 | 9003 | 2023-07-11 19:11 |
Expected output
Hourly CTR and two-proportion z-test comparing each hour to the opposite group (peak vs non-peak) over the last 7 days anchored to the latest impression timestamp.
| comp_conversions | comp_impressions | comparison_group | conversions_hour | ctr_hour | hour_of_day | impressions_hour | is_peak | p_value |
|---|---|---|---|---|---|---|---|---|
| 2 | 2 | peak | 0 | 0 | 14 | 1 | false | 0.0833 |
| 0 | 1 | non-peak | 1 | 1 | 18 | 1 | true | 0.1573 |
| 0 | 1 | non-peak | 1 | 1 | 19 | 1 | true | 0.1573 |
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)).