Retrieve Top Five Ads by Conversions in 30 Days

ads

ad_id | advertiser_id | created_at

1 | 101 | 2024-06-01

2 | 102 | 2024-06-03

3 | 101 | 2024-06-10

​

ad_impressions

impression_id | ad_id | user_id | impression_date

1001 | 1 | 501 | 2024-06-15

1002 | 2 | 502 | 2024-06-15

1003 | 1 | 503 | 2024-06-16

1004 | 3 | 501 | 2024-06-17

1005 | 2 | 504 | 2024-06-18

​

ad_conversions

conversion_id | impression_id | revenue | conversion_date

2001 | 1001 | 5.00 | 2024-06-15

2002 | 1003 | 2.50 | 2024-06-16

2003 | 1005 | 7.00 | 2024-06-18

Scenario

Analyze advertising performance; return the top N ads by conversions in the last M days using three related tables.

Question

Write SQL to return the five ads with the highest number of conversions in the past 30 days. Output ad_id, advertiser_id, total_conversions, sorted by total_conversions DESC. Exclude ads with zero conversions and handle impressions that never converted.

Hints

LEFT JOIN impressions to conversions, filter on date range, GROUP BY ad_id.