Heavy Ad Exposure

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

L5

Asked in

Shell

Problem

A user counts as heavily ad-exposed if they have 3 or more impressions from a single campaign, or 5 or more total impressions spread across at least 2 different campaigns. Return the IDs of the users who qualify.

Saturated with ads. Is it too much?

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 problem really is

Strip off the ad-exposure costume and this is an OR of two thresholds that live at two different grain levels. Rule one is about a single campaign's count for a user; rule two is about a user's grand total and their campaign variety. The skill being probed: can you compute both from one rollup instead of scanning the table twice and stitching the halves with a UNION? The trap is the grain. People evaluate the 3-impression rule against a user's total, which silently qualifies anyone busy across many campaigns and breaks the whole definition.

The move that cracks it

Roll the table up to one row per (user_id, ad_campaign) with its impression count. Now everything you need is a second-level aggregate over that result, per user: MAX of those campaign counts is the hottest single campaign (rule one), SUM is the user's total impressions and COUNT is how many distinct campaigns they touched (rule two). One pass, no UNION, no dedup.

Step 1: Collapse to per-campaign counts first

Build per_campaign: GROUP BY user_id, ad_campaign with COUNT(*). Filter out NULL user_id here, before any counting, so anonymous impressions never pollute a real user's totals.

Step 2: Read both rules off the rollup

Aggregate per_campaign by user_id into per_user: MAX(impressions) is the hottest campaign, SUM(impressions) is the total, COUNT(*) is the distinct-campaign count. Then filter with a plain WHERE: hottest_campaign >= 3 (rule one) OR (total_impressions >= 5 AND campaign_count >= 2) (rule two). OR them so either path qualifies.

WITH per_campaign AS (
  SELECT user_id, ad_campaign, COUNT(*) AS impressions
  FROM ad_impressions
  WHERE user_id IS NOT NULL
  GROUP BY user_id, ad_campaign
),
per_user AS (
  SELECT user_id,
         MAX(impressions) AS hottest_campaign,
         SUM(impressions) AS total_impressions,
         COUNT(*) AS campaign_count
  FROM per_campaign
  GROUP BY user_id
)
SELECT user_id
FROM per_user
WHERE hottest_campaign >= 3
   OR (total_impressions >= 5 AND campaign_count >= 2)

UNION of two scans

Two full GROUP BY passes over a 400M-row table, then a UNION to merge and dedup. More SQL, two scans, and the per-campaign threshold is easy to misplace.

One rollup, OR the rules

Collapse to per-(user, campaign) counts once, aggregate to one row per user, then read both rules off MAX, SUM, and COUNT in a single WHERE over that much smaller set.

Common follow-up questions

• Now restrict the 5-impression rule to impressions within any rolling 7-day window. (Tests whether they can add a windowed condition on top of the rollup.)
• Return which rule each user qualified under, and both counts. (Tests extending the output beyond a flag to a reason.)
• Of these heavily exposed users, which ones never generated any revenue? (Tests joining the qualifier set back to revenue.)