Coding questions · Meta
Retrieve Ad Metrics and Rates for Last 7 Days
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.
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.
Daily Ad Metrics with 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.
Table ads
| column | type | constraints |
|---|---|---|
| ad_id | INTEGER | PRIMARY KEY, NOT NULL |
| advertiser_id | INTEGER | NOT NULL |
| ad_type | VARCHAR(50) | NOT NULL |
Sample data:
| ad_id | ad_type | advertiser_id |
|---|---|---|
| 101 | image | 1001 |
| 102 | video | 1002 |
| 103 | carousel | 1001 |
Table impressions_clicks
| column | type | constraints |
|---|---|---|
| event_id | INTEGER | PRIMARY KEY, NOT NULL |
| ad_id | INTEGER | NOT NULL, FOREIGN KEY -> ads.ad_id |
| is_click | INTEGER | NOT NULL, CHECK (is_click IN (0,1)) |
| event_time | DATE | NOT NULL |
Sample data:
| ad_id | event_id | event_time | is_click |
|---|---|---|---|
| 101 | 1 | 2023-07-01 | 0 |
| 101 | 2 | 2023-07-01 | 1 |
| 102 | 3 | 2023-07-01 | 0 |
| 103 | 4 | 2023-07-02 | 1 |
Table conversions
| column | type | constraints |
|---|---|---|
| conv_id | INTEGER | PRIMARY KEY, NOT NULL |
| event_id | INTEGER | NOT NULL, FOREIGN KEY -> impressions_clicks.event_id |
| ad_id | INTEGER | NOT NULL, FOREIGN KEY -> ads.ad_id |
| conv_time | DATE | NOT NULL |
Sample data:
| ad_id | conv_id | conv_time | event_id |
|---|---|---|---|
| 101 | 900 | 2023-07-01 | 2 |
| 103 | 901 | 2023-07-02 | 4 |
| 101 | 902 | 2023-07-03 | 2 |
Expected output
One row per ad_id per date between 2025-05-26 and 2025-06-01 (inclusive). Impressions = count of events with is_click = 0 on that date. Clicks = count of events with is_click = 1 on that date. Conversions are attributed to the date of the click event (join conversions to impressions_clicks on event_id and ad_id and use impressions_clicks.event_time as the conversion date), not conv_time. CTR = clicks / impressions (0.0 when impressions = 0). conversion_rate = conversions / clicks (0.0 when clicks = 0). Using the provided sample_data, all impression and conversion events occur in 2023, so there are no events in the 2025-05-26 to 2025-06-01 window and all metrics are zero.
| ad_id | clicks | conversion_rate | conversions | ctr | event_date | impressions |
|---|---|---|---|---|---|---|
| 101 | 0 | 0 | 0 | 0 | 2025-05-26 | 0 |
| 102 | 0 | 0 | 0 | 0 | 2025-05-26 | 0 |
| 103 | 0 | 0 | 0 | 0 | 2025-05-26 | 0 |
| 101 | 0 | 0 | 0 | 0 | 2025-05-27 | 0 |
| 102 | 0 | 0 | 0 | 0 | 2025-05-27 | 0 |
| 103 | 0 | 0 | 0 | 0 | 2025-05-27 | 0 |
| 101 | 0 | 0 | 0 | 0 | 2025-05-28 | 0 |
| 102 | 0 | 0 | 0 | 0 | 2025-05-28 | 0 |
| 103 | 0 | 0 | 0 | 0 | 2025-05-28 | 0 |
| 101 | 0 | 0 | 0 | 0 | 2025-05-29 | 0 |
| 102 | 0 | 0 | 0 | 0 | 2025-05-29 | 0 |
| 103 | 0 | 0 | 0 | 0 | 2025-05-29 | 0 |
| 101 | 0 | 0 | 0 | 0 | 2025-05-30 | 0 |
| 102 | 0 | 0 | 0 | 0 | 2025-05-30 | 0 |
| 103 | 0 | 0 | 0 | 0 | 2025-05-30 | 0 |
| 101 | 0 | 0 | 0 | 0 | 2025-05-31 | 0 |
| 102 | 0 | 0 | 0 | 0 | 2025-05-31 | 0 |
| 103 | 0 | 0 | 0 | 0 | 2025-05-31 | 0 |
| 101 | 0 | 0 | 0 | 0 | 2025-06-01 | 0 |
| 102 | 0 | 0 | 0 | 0 | 2025-06-01 | 0 |
| 103 | 0 | 0 | 0 | 0 | 2025-06-01 | 0 |
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).
- Generate a date calendar from 2025-05-26 to 2025-06-01 and cross join with ads to include dates with zero activity.
- Use COALESCE and NULLIF in CASE expressions to avoid division by zero when computing CTR and conversion_rate.