# Crossed Signals > No shared key, only a shared clock. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem Our ad platform and our ops alerting system share no common key, only a wall clock: line up each alert with the ad impressions that landed in the same hour. For each campaign, report how many alerts it drew and how many of those were marked resolved, most alerts first. ## 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, so the only glue is coincidence in time: 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 tallies each alert once per impression and your alert counts quietly become impression counts. The campaign with the most ad traffic floats to the top, which is not the question. --- ### 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) and match on that string. 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, wrap the alert tally in DISTINCT: COUNT(DISTINCT alert_id). Without it, 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 inside the conditional, so a resolved alert that matched several impressions still counts once. Read resolved off the status column, not the resolved timestamp column, and note that the two can disagree. #### Step 4: Group and order Group by ad_campaign and order by alert_count descending so the busiest campaigns surface first, breaking ties by resolved_count and then name for a stable, readable result. --- ### The solution **Crossed Signals** ```sql 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 ORDER BY alert_count DESC, resolved_count DESC, ad_campaign ``` > **Cost analysis** > > 300M impressions bucketed against 20M alerts on SUBSTR(ts, 1, 13) throws away partition pruning and every index: the optimizer builds a synthetic string key and hash-joins the pair with two full scans. In production you would materialize an hour_bucket column via DATE_TRUNC('hour', ...), partition on it, and turn the pair of full scans into a co-partitioned merge. > **Interviewers watch for** > > Two questions separate the seniors. First, out loud: how do alerts even relate to campaigns? There is no key, you are inferring it from a shared hour. Second: does the join fan out, and if so what stops the double count? A candidate who reaches for COUNT(DISTINCT alert_id) without being nudged has already seen the trap. > **Common pitfall** > > Writing COUNT(alert_id) instead of COUNT(DISTINCT alert_id). The hour bucket is many-to-many: one alert co-occurs with every impression in its hour, so each alert is counted once per impression. The campaign with the most impressions rises to the top, not the one with the most alerts. > **Two signals, one word** > > The table hands you two ways to read resolved: a status column whose value can be resolved, and a separate resolved timestamp that is sometimes filled even when the status still says acknowledged. They disagree on real rows. Pick the one the question means (status here), state why, and do not silently switch to the timestamp mid-query. --- ### 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 bucketing finer 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/crossed_signals) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.