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

The security team is measuring how reliably users complete two-factor authentication, counting only the pushes that were actually delivered. For each client platform, find the confirmation rate, the share of delivered pushes the user opened, and rank the platforms from the most reliable down to the weakest.

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

Beneath the security dashboard, this is a per-platform ratio where the numerator and denominator disagree about what NULL means. Anyone can type SUM(CASE ...) / COUNT(*) and group by platform. Two quiet things separate people. First, SUM(...) / COUNT(*) is integer over integer, so a 2-of-3 rate floors to 0 instead of 0.67. Second, a delivered push with opened IS NULL still belongs in the denominator but must never count as confirmed. Miss either one and you hand the security team confirmation rates that are confidently, invisibly wrong.

Break down the requirements

Step 1: Filter to qualifying rows

Apply the WHERE clause to isolate the delivered subset before computing the ratio, so failed and bounced pushes never touch the math.

Step 2: Group by platform

GROUP BY platform produces one output row per distinct client platform.

Step 3: Compute the ratio with CASE and NULLIF

The numerator is SUM(CASE WHEN opened = 1 THEN 1 ELSE 0 END); NULL fails the = 1 test and adds 0. Wrapping the denominator in NULLIF(COUNT(*), 0) prevents a divide-by-zero on any empty group.

Step 4: Round and order

Use ROUND(..., 2) for clean decimal output and sort by confirmation_rate descending.

The solution

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

Common follow-up questions

• What if the data volume increased 10x? (Tests scaling awareness: would the query plan change, and where is the bottleneck?)
• How would you handle this if the data arrived as a streaming feed? (Tests whether the candidate can think beyond batch SQL toward incremental computation.)
• Could you rewrite this using a CTE instead of a subquery, or vice versa? (Tests fluency with query refactoring and understanding that CTEs and subqueries are often interchangeable.)
• How would the results change if you used COUNT(DISTINCT col) instead of COUNT(*)? (Tests understanding of distinct vs total counting and when deduplication matters.)