The Ones Still Listening

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

L3

Asked in

Rockerbox

Problem

The engagement team is auditing push reach for the quarterly product review, starting from the full registered user base and attaching every notification we sent each person. Users we have never sent a push to still have to count in that audit. What percentage of all those audit lines went to an active account and were opened by the recipient?

A buzz only counts when someone who is still around answers it.

Tables

push_notifs

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this problem is really about

Strip the costume and this is a denominator-survival test. The rate is measured against the whole registered base, and half of that base is silent: on this data 100 of the 200 users were never sent a push. If they fall out of the denominator, the metric quietly becomes 'engaged share of people we already contacted', which always reads better. Anyone can write the conditional sum. The separator is anchoring on users and keeping the un-reached with an outer join. Inner-join instead (or aggregate push_notifs alone) and the denominator shrinks from 300 lines to 200, and the number climbs from 5.0% to 7.5% on the exact same 15 opens. The quieter trap is arithmetic: two integer counts divided truncate to 0.

Walking the requirements

Step 1: Anchor on the full audience with a LEFT JOIN

users is the driving table, so every registered person survives, and push_notifs is the optional side joined on user_id. Flip the direction or use an inner join and the 100 silent users vanish from the total.

Step 2: Flag active opens with CASE

Yield 1 when account_status = 'active' AND opened = 1, otherwise 0, then SUM it. A real send with opened 0 or NULL, and the NULL opened an un-reached user carries through the join, all fall to the ELSE branch and count as not opened.

Step 3: Make the numerator real before dividing

CAST the sum to REAL, divide by COUNT(*), then multiply by 100. The order matters: 15 / 300 between two integers is 0, and 0 times 100 is still 0. Multiplying by 100.0 first also escapes truncation; dividing two bare counts never does.

Step 4: Return one scalar percentage

The whole audience collapses to one value with no grouping and no ordering: a fractional percent, exactly the shape a review-deck metric wants.

The solution

SELECT CAST(SUM(CASE WHEN u.account_status = 'active' AND pn.opened = 1 THEN 1 ELSE 0 END) AS REAL) / COUNT(*) * 100 AS active_open_pct
FROM users u
LEFT JOIN push_notifs pn ON pn.user_id = u.user_id

Inner join (wrong)

Only users who received a notification survive. The denominator is 200 lines and the reported rate is 7.5%, flattering engagement by dropping everyone never contacted.

Left join from users (correct)

Every registered user stays, un-reached ones as a single NULL line. The denominator is all 300 lines and the rate is an honest 5.0% of the whole audience.

Common follow-up questions

• Most of the user base was never sent a push. Should those users count, and how does your join keep them in the denominator? (Tests whether the candidate treats an un-contacted user as part of the base and knows the outer join keeps them.)
• `opened` is NULL both for some real sends and for every un-reached user via the join. Why does your `CASE` treat both as not opened? (Tests NULL handling inside an aggregate and the two sources of a NULL `opened`.)
• At 4M users and 50M notifications, how would you keep the join from becoming the bottleneck? (Tests join strategy and indexing or partitioning on `user_id`.)
• Now the team wants this rate per `account_status`. What changes, and how do un-reached users stay in each group's total? (Tests extending a scalar metric into a breakdown without breaking the full-audience denominator.)