Click 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

Problem

An analyst reconciling the monthly campaign review found click revenue in ad_impressions booked against impressions with no user attached, and finance refuses to credit that money to any campaign. Total each campaign's click revenue from user-attributed impressions only, keeping just the campaigns that bring in more than five dollars.

Which campaigns are earning their keep?

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

Two filters wearing one costume

This is a row filter and a group filter dressed up as an ROI review. Anyone can write the SUM. What separates candidates is knowing which condition belongs to a single impression and which belongs to a whole campaign. Unattributed revenue is a fact about one row, so it goes in WHERE. The five-dollar bar is a fact about a total, so it goes in HAVING. Swap them and you either credit money finance rejects or delete every click from the report.

Step 1: Drop impressions with no user

Keep only rows where user_id is not null. An impression nobody can tie to a person may still carry revenue, and leaving it in quietly inflates the campaign it sits under.

Step 2: Total per campaign

GROUP BY ad_campaign with SUM(revenue) gives one attributed total per campaign. No click filter needed: non-clicked impressions hold NULL revenue and SUM skips them.

Step 3: Cut on the total

HAVING SUM(revenue) > 5 runs after the totals exist and drops the campaigns that did not clear the bar. This is the only place an aggregate is allowed in a predicate.

SELECT ad_campaign, SUM(revenue) AS click_revenue
FROM ad_impressions
WHERE user_id IS NOT NULL
GROUP BY ad_campaign
HAVING SUM(revenue) > 5

Cutoff on the row

WHERE revenue > 5 keeps only impressions worth more than five dollars on their own. Single clicks are worth cents, so the report comes back empty.

Cutoff on the group

HAVING SUM(revenue) > 5 lets many small clicks add up first, then judges the campaign by its total. That is what the review actually asks.

Common follow-up questions

• What if `revenue` could be negative because of refunds? (Whether netting refunds into the total is right for an ROI review, or whether they belong in a separate column.)
• How would you show unattributed revenue next to attributed revenue in one row per campaign? (Conditional aggregation over `user_id` inside a single `GROUP BY`.)
• What if the cutoff were relative, like campaigns above the median total? (Comparing each group to a statistic computed over all groups.)