Crossed Signals

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Etsy

Problem

Report, for each ad campaign, how many alerts it drew and how many of those alerts have a status of resolved. The ops team credits an alert to a campaign when one of that campaign's ad_impressions landed in the same calendar hour, on the same date, as the alert fired in alert_events, because the ad platform and the alerting system share no key, only a wall clock.

No shared key, only a shared clock.

Tables

alert_events

Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT

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

Strip the costume and this is a many-to-many join between two tables that were never meant to touch. ad_impressions and alert_events share no foreign key. The only link is timing: an alert and an impression that happened in the same hour. The real skill being probed is whether you notice that the hour bucket fans out. One alert hour matches every impression in that hour, so a plain COUNT(alert_id) tallies each alert once per impression. Your alert counts quietly become impression counts, and the campaign with the most ad traffic floats to the top, which is not what the question asks.

Walk the build

Step 1: Name the join key

There is no foreign key between the tables. Bucket both timestamps to the hour with SUBSTR(impression_time, 1, 13) and SUBSTR(fired_at, 1, 13), then match on that string. Keep it an inner join, so a campaign whose impressions never line up with an alert hour drops out. Say this out loud before writing SQL, so the interviewer knows you saw the missing key rather than assuming one.

Step 2: Dedupe the fan-out

Because the bucket join is many-to-many, count with COUNT(DISTINCT alert_id). Without the distinct, every alert is multiplied by the number of impressions sharing its hour, and the metric stops meaning what its name says.

Step 3: Count resolved conditionally

COUNT(DISTINCT CASE WHEN status = 'resolved' THEN alert_id END) keeps the dedup around the conditional, so a resolved alert that matched several impressions still counts once. Read resolved from status, not from the resolved timestamp column; the two disagree on real rows.

Step 4: Group, and order only for people

GROUP BY ad_campaign gives one row per campaign. Grading ignores row order, so the answer needs no ORDER BY. If a person will read the output, sort by alert_count descending for them, but that is presentation, not part of the answer.

The solution

WITH matched AS (
  SELECT ai.ad_campaign,
         ae.alert_id,
         ae.status
  FROM ad_impressions ai
  INNER JOIN alert_events ae
    ON SUBSTR(ai.impression_time, 1, 13) = SUBSTR(ae.fired_at, 1, 13)
)
SELECT ad_campaign,
       COUNT(DISTINCT alert_id) AS alert_count,
       COUNT(DISTINCT CASE WHEN status = 'resolved' THEN alert_id END) AS resolved_count
FROM matched
GROUP BY ad_campaign

Common follow-up questions

Common follow-up questions

• Two campaigns tie for the most alerts. How do you present that? (Checks that ties are surfaced by ordering rather than dropped by `LIMIT 1` or `ROW_NUMBER()`.)
• How would you redesign the schema so this hour-bucket match is not needed? (Tests whether you can propose adding an `ad_campaign_id` to `alert_events` at ingest, retiring the hour-bucket heuristic entirely.)
• Change the window from same hour to within 15 minutes. (Forces an inequality range join on timestamps and a discussion of finer buckets versus `BETWEEN`.)
• How do you handle an alert that fires at 11:59 and an impression at 12:00? (Surfaces that `SUBSTR(ts, 1, 13)` truncates and misses co-occurrence across an hour boundary.)