Proof of Presence

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

Meta

Problem

List each client platform's two-factor confirmation rate and ignored rate from push_notifs_2fa, both as two-decimal shares of the pushes that were actually delivered, with the highest confirmation rate first. The security analyst who asked counts a push as confirmed when the user opened it and as ignored only when the client recorded it as unopened, so a delivery with no recorded outcome stays in the total but is neither.

Two-factor sent. How many confirmed?

Tables

push_notifs_2fa

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

This is a three-state flag dressed up as a security dashboard. opened holds a 1 for a confirmed push, a 0 for one the client saw go unopened, and a blank when the client never reported back. You have to split that one column into two shares over the same delivered denominator. Anyone can filter to delivered and split by platform. What separates people is counting the right thing: COUNT(opened) counts the zeros too and scores Android at 1.00, and an ignored count that sweeps in the blanks doubles web's ignored rate. Both look like plausible dashboard numbers, which is exactly why nobody catches them.

Counts the column

COUNT(opened) skips only NULLs, so Android's three delivered pushes (1, 1, 0) give 3 and a confirmation rate of 1.00.

Counts the confirmations

COUNT(NULLIF(opened, 0)) turns each 0 into a NULL first, so only the 1s survive: 2 of 3, a rate of 0.67.

Building it

Step 1: Filter to delivered pushes first

WHERE status = 'delivered' fixes the denominator. It also removes a bounced Android push that is flagged as opened: the seed contradicts itself there, and skipping the filter quietly lifts Android to 0.75.

Step 2: Split at platform

One row per client platform. COUNT(*) is then that platform's delivered pushes, blanks included, which is what both shares divide by.

Step 3: Count confirmations with NULLIF

COUNT ignores NULLs, so NULLIF(opened, 0) hides the unopened pushes from it. A blank flag is already NULL and stays out. What is left is exactly the 1s.

Step 4: Count ignored pushes with a CASE

SUM(CASE WHEN opened = 0 THEN 1 ELSE 0 END) scores only an explicit 0. A blank fails opened = 0 and adds nothing, so web's unknown push lands in neither share and web reads 0.33 ignored, not 0.67.

Step 5: Divide, round, sort

1.0 * keeps each division fractional, ROUND(..., 2) gives the two decimals the analyst reads, and the result is sorted on confirmation_rate descending.

SELECT
    platform,
    ROUND(1.0 * COUNT(NULLIF(opened, 0)) / COUNT(*), 2) AS confirmation_rate,
    ROUND(1.0 * SUM(CASE WHEN opened = 0 THEN 1 ELSE 0 END) / COUNT(*), 2) AS ignored_rate
FROM push_notifs_2fa
WHERE status = 'delivered'
GROUP BY platform
ORDER BY confirmation_rate DESC

Common follow-up questions

• The analyst now wants the same two shares per platform per month. What changes? (Adding a time grain from `sent_at` and checking the partition pruning it buys.)
• Some clients start sending `opened = 2` for a push dismissed by the OS. Does your confirmation count still hold? (Whether `COUNT(NULLIF(opened, 0))` still means 'confirmed' once the flag gains a fourth state.)
• How would you surface the share of deliveries with no recorded outcome as a third column? (Making the blank bucket explicit without double-counting it.)