Retrieve Ad Metrics and Rates for Last 7 Days

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.