Katkat
Back

Rank Ads by Conversion Rate for Top 10 Performers

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This question evaluates data manipulation and analytics skills for computing time-windowed ad performance metrics, including aggregations of impressions and revenue, join logic between impression and conversion records, conversion-rate calculation, and ranking.

ad

id | advertiser_id | created_at

1 | 101 | 2023-07-01

2 | 102 | 2023-07-05

3 | 101 | 2023-07-10

​

impression

id | ad_id | user_id | timestamp

10 | 1 | 555 | 2023-07-11 10:00

11 | 1 | 556 | 2023-07-11 10:05

12 | 2 | 557 | 2023-07-12 09:00

​

conversion

id | impression_id | revenue | timestamp

100 | 10 | 5.00 | 2023-07-11 10:10

101 | 12 | 10.00 | 2023-07-12 09:15

Scenario

Advertising platform wants to track ad effectiveness over the last 30 days.

Question

Given tables ad, impression and conversion, write SQL to return for every ad_id: total impressions, total conversions, conversion_rate and total_revenue for the past 30 days. Extend the query to rank ads by conversion_rate and return the top 10 performers.

Hints

Think joins (impression → conversion), date filters, group-by ad_id, and safe division for rates.