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.
Overview: 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.
Three tables store information about ads, their impressions/clicks, and downstream conversions.
Write a SQL query that returns, for each ad and each calendar date from 2025-05-26 through 2025-06-01 (inclusive), the number of impressions, clicks, conversions, CTR (clicks/impressions), and conversion_rate (conversions/clicks). Conversions should be attributed to the date of the corresponding click event (join on impressions_clicks.event_id and ad_id), not the conv_time date. Return one row per ad per date, including dates with zero activity, and order the result by event_date and ad_id.
Tables
ads(ad_id INTEGER, advertiser_id INTEGER, ad_type VARCHAR(50))
impressions_clicks(event_id INTEGER, ad_id INTEGER, is_click INTEGER, event_time DATE)
conversions(conv_id INTEGER, event_id INTEGER, ad_id INTEGER, conv_time DATE)
Hints
- Join impressions_clicks and conversions on event_id and ad_id to attribute conversions to clicks.
- Aggregate with conditional sums for impressions (is_click = 0) and clicks (is_click = 1).