Top Ad Campaigns by Revenue

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Audible

Problem

The revenue leaderboard only has room for five campaigns. Across every impression in ad_impressions, give the ad ops team the five campaigns with the highest total ad revenue, biggest first.

Every campaign has a bottom line. Stack them up.

Tables

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

Expected output

Worked solution and explanation

What this really is

Strip off the leaderboard costume and this is a grouped total finished with a top-N cut. Summing revenue per campaign is the half everyone writes. What actually gets graded is whether you finish the job: sort the campaigns by that total and keep only the five the board has room for. Leave the cut off and you hand back all eight campaigns in ad_impressions, three of which never belonged on the board. Sort raw impression rows before totaling and every number you return is one impression's revenue, not a campaign's.

Break down the requirements

Step 1: Total revenue per campaign

GROUP BY ad_campaign with SUM(revenue) collapses the many impression rows per campaign into one total. Unclicked impressions carry a NULL revenue, and SUM skips them on its own, so they neither zero out a campaign nor drop it.

Step 2: Sort, then keep the leaders

ORDER BY total_revenue DESC puts the biggest earners on top, then LIMIT 5 keeps the leaders. The order is what gives the cut its meaning: without it, LIMIT 5 returns five arbitrary campaigns.

The solution

SELECT
    ad_campaign,
    SUM(revenue) AS total_revenue
FROM ad_impressions
GROUP BY ad_campaign
ORDER BY total_revenue DESC
LIMIT 5

Common follow-up questions

• If some impressions have `NULL` revenue (organic placements), how does `SUM` treat those rows? (Tests knowing that `SUM` skips `NULL` values, and when a `NULL` should instead mean zero.)
• If two campaigns tie on total revenue right at the fifth spot, how do you make the cut deterministic? (Tests whether a tiebreaker is needed so the fifth and sixth campaigns cannot swap between runs.)
• How would you add a column showing each campaign's percentage of total revenue? (Tests using a window `SUM` or a scalar subquery for the denominator alongside the grouped query.)