Left on Read

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

L4

Asked in

Amazon

Problem

Our push notification system records an opened flag on every notification along with the campaign that sent it. Across every campaign that has sent notifications, find the ones whose opens outnumber those of the average campaign.

Every campaign fights for the tap. Find the ones the field is leaving behind.

Tables

push_notifs

Columns: notif_id INTEGER, user_id INTEGER, title TEXT, platform TEXT, status TEXT, sent_at TEXT, opened INTEGER, campaign TEXT

Expected output

Worked solution and explanation

What this really is

Break down the requirements

Step 1: Tally opens with SUM, not a pre-filter

An open is opened = 1; a 0 or a NULL is not. Count opens with SUM(opened): it adds up the set flags and ignores the zeros and the blanks, so you get each campaign's open tally without ever writing WHERE opened = 1. That matters, because that filter would erase the campaigns that earned no opens, and those campaigns still belong in the field you are measuring against.

Step 2: Drop the phantom no-campaign group

Filter campaign IS NOT NULL before you group, in the standings and again in the average. A notification with no campaign is not a campaign, and its rows are the single largest bucket in the table. Leave them in and they both win the standings outright with 21 opens and inflate the opens the average is built from, poisoning the very bar you are comparing everyone against.

Step 3: Build the yardstick at the campaign grain

The average campaign is total opens divided by the number of campaigns: SUM(opened) * 1.0 / COUNT(DISTINCT campaign). The trick is the denominator. COUNT(DISTINCT campaign) counts every campaign that sent anything, including the ones that earned zero opens, so those quiet campaigns still weigh in the average even though they add nothing to the numerator. Keep the campaigns whose tally clears that bar.

The solution

SELECT campaign, SUM(opened) AS open_count
FROM push_notifs
WHERE campaign IS NOT NULL
GROUP BY campaign
HAVING SUM(opened) > (
    SELECT SUM(opened) * 1.0 / COUNT(DISTINCT campaign)
    FROM push_notifs
    WHERE campaign IS NOT NULL
)

AVG over rows (wrong)

AVG(opened) across every notification yields the per-notification open rate, about 0.35 here. Any campaign that logged even one open clears it, so the filter stops separating strong campaigns from weak ones and the grain is simply wrong.

average over campaigns (right)

SUM(opened) * 1.0 / COUNT(DISTINCT campaign) yields the opens the typical campaign earned, 5 on this data. Keeping the campaigns whose tally clears that bar surfaces the genuine over-performers.

Common follow-up questions

• A couple of blockbuster campaigns drag the mean upward, hiding some real over-performers below it. Would you switch to a median as the threshold, and how would you compute one in SQL? (Tests understanding that the mean is sensitive to skew and awareness of percentile approaches.)
• How would you also report each campaign's open rate (opens over sends) alongside its open count? (Tests conditional counting: opens over sends per campaign.)
• What happens to your threshold if a campaign sends notifications but records no opens, and where does it belong in the calculation? (Tests whether the candidate realizes zero-open campaigns must remain in the denominator of the average.)