Retrieve Ad Metrics and Rates for Last 7 Days
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
ads
+--------+---------------+----------+
| ad_id | advertiser_id | ad_type |
+--------+---------------+----------+
| 101 | 1001 | image |
| 102 | 1002 | video |
| 103 | 1001 | carousel |
+--------+---------------+----------+
impressions_clicks
+----------+--------+----------+------------+
| event_id | ad_id | is_click | event_time |
+----------+--------+----------+------------+
| 1 | 101 | 0 | 2023-07-01 |
| 2 | 101 | 1 | 2023-07-01 |
| 3 | 102 | 0 | 2023-07-01 |
| 4 | 103 | 1 | 2023-07-02 |
+----------+--------+----------+------------+
conversions
+---------+----------+--------+------------+
| conv_id | event_id | ad_id | conv_time |
+---------+----------+--------+------------+
| 900 | 2 | 101 | 2023-07-01 |
| 901 | 4 | 103 | 2023-07-02 |
| 902 | 2 | 101 | 2023-07-03 |
+---------+----------+--------+------------+
##### Scenario
Three tables store information about ads, their impressions/clicks, and downstream conversions.
##### Question
Write a SQL query that returns, for each ad and calendar date in the last 7 days, impressions, clicks, conversions, CTR (clicks/impressions), and conversion_rate (conversions/clicks). Order by date, ad_id.
##### Hints
Join impressions_clicks and conversions on event_id/ad_id, aggregate with conditional sums, handle division by zero.
Quick Answer: This question evaluates proficiency in SQL-based data manipulation including joins, conditional aggregation, time-based filtering, and computation of advertising metrics such as impressions, clicks, CTR, and conversion rate.