Feature Flag Engagement Impact

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

General Assembly

Problem

The growth team wants to understand session activity around feature flag launches. For each feature flag, return the flag name, a display name (underscores replaced with spaces), whether the flag is currently enabled, and how many user sessions started in the 30 days following the flag's created date. Flags with zero qualifying sessions should still appear with a session count of zero. Sort by flag name.

Flags on versus flags off. The engagement gap.

Tables

feat_flags

Columns: flag_id INTEGER, flag_name TEXT, enabled INTEGER, rollout REAL, owner TEXT, created TEXT, updated TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

push_notifs

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

The mental model

Experimentation teams use this to check one habit: do you treat a date-bounded inequality as a JOIN condition or a WHERE filter? Both compute different answers, and only one preserves flags that had zero qualifying sessions. The interviewer is watching whether you reach for LEFT JOIN ... ON DATE before they have to ask, and whether you say COUNT(us.session_id) instead of COUNT(*) without prompting.

The two traps that will burn you

Step 1: Trap 1: WHERE kills your LEFT JOIN

If you write the window filter in WHERE (WHERE us.session_start >= ff.created AND us.session_start < ...), the LEFT JOIN silently becomes an INNER JOIN. SQL evaluates ON first, producing NULL rows for unmatched flags, then WHERE filters those NULLs out (because NULL fails any comparison). Result: flags with zero sessions vanish from your output and you fail the 'still appear with zero' requirement. Fix: put the window predicate inside ON.

Step 2: Trap 2: COUNT(*) lies on a LEFT JOIN

On a LEFT JOIN, unmatched flags produce one synthetic row with NULL in every right-side column. COUNT(*) counts that synthetic row as 1, so a flag with no sessions reports launch_window_sessions = 1. COUNT(us.session_id) skips the NULL and correctly reports 0. Always count a specific right-side column when LEFT JOIN is involved.

The solution

SELECT
    ff.flag_name,
    REPLACE(ff.flag_name, '_', ' ') AS display_name,
    ff.enabled,
    COUNT(us.session_id) AS launch_window_sessions
FROM feat_flags ff
LEFT JOIN user_sessions us
    ON DATE(us.session_start) >= DATE(ff.created)
   AND DATE(us.session_start) <  DATE(ff.created, '+30 days')
GROUP BY ff.flag_id, ff.flag_name, ff.enabled
ORDER BY ff.flag_name

Common follow-up questions

• Walk me through what happens if I move the date predicate from ON into WHERE. Will the output change? (The interviewer is testing whether the candidate knows that ON-clause filters preserve LEFT JOIN semantics while WHERE filters collapse them. A common follow-up after the candidate submits a wrong query.)
• If I swap COUNT(`us.session_id`) for COUNT(*), what value does a flag with zero matching sessions now report, and why? (Tests COUNT semantics on outer joins. The candidate should explain that COUNT(*) counts synthetic NULL rows generated by LEFT JOIN.)
• The product team now wants 'rolling 7-day' instead of '30-day fixed'. What changes? (Tests time-bucketing literacy. Real answer: yes, recompute the windows from creation, do not assume midnight-aligned days.)
• The query takes 4 minutes in production. The sessions table is partitioned by day. What is the first thing you would change? (Tests partition awareness. `user_sessions` is partitioned on `session_start`; the planner can prune partitions if you push down a global date range, but per-flag windows defeat that. The fix is generating the date range as min(created) to max(created)+30 globally.)