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.
Quick Answer: 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.