Break Through

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

Domain

SQL

Difficulty

medium

Seniority

L3

Problem

The renewal list for next quarter's ad budget looks padded to the analyst signing it off, who wants to keep only the campaigns in ad_impressions where more than one in five impressions turned into a click. Surface those campaigns with their click-through rate and the revenue they brought in, highest click-through rate first.

A click is the only vote that counts.

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

Underneath the budget audit this is a ratio filtered by its own value. The click-through rate, SUM(clicked) over COUNT(*), does not exist until the rows are rolled up per ad_campaign, so the more-than-one-in-five bar has to judge the rolled-up rate, never a single impression. Candidates blow it two ways. Some filter in WHERE, where each row only carries a 0 or 1 flag. Others divide two integers, which floors every campaign below 100% to 0, so the bar drops all eight campaigns and the query returns nothing.

Build it in three moves

Step 1: Roll up per ad_campaign

Every metric here is a per-campaign number, so ad_impressions is grouped by ad_campaign first. COUNT(*) is the impressions served, including the many rows where revenue is NULL.

Step 2: Compute the rate in decimal

100.0 * SUM(clicked) / COUNT(*) gives the percentage. Summing the 0/1 clicked flag counts clicks, and the 100.0 forces decimal division. SUM(revenue) skips the NULLs on unclicked impressions, but those rows stay in the denominator.

Step 3: Judge the group, then sort

The bar runs in HAVING on the unrounded ratio, so rounding can never push a campaign across the line. The output rounds to two decimals and lists the highest rate first. FLASH_SALE_48H and NEW_USER_ACQU both sit at 26.67, and either may come first.

The solution

SELECT
    ad_campaign,
    ROUND(100.0 * SUM(clicked) / COUNT(*), 2) AS ctr_pct,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS impressions
FROM ad_impressions
GROUP BY ad_campaign
HAVING 100.0 * SUM(clicked) / COUNT(*) > 20
ORDER BY ctr_pct DESC

Filtering impressions

WHERE clicked = 1 keeps only clicked rows. Every surviving campaign then shows a 100% rate, and impressions quietly counts clicks.

Filtering campaigns

HAVING 100.0 * SUM(clicked) / COUNT(*) > 20 judges each campaign's full rate. Six of the eight campaigns clear it.

Common follow-up questions

• What if the bar were the average click-through rate across all campaigns? (Tests computing a global figure once and comparing each campaign's rate against it.)
• How would you stop a campaign with a handful of impressions from clearing the bar by luck? (Tests adding a minimum-volume condition alongside the rate.)
• What if you needed weekly click-through trends per campaign? (Tests bucketing `impression_time` by week and grouping on the bucket too.)