Peak Non-Converting Month

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

ESPN

Problem

A retention review wants the single month when the most people opened the app from a recognized device, browsed, and still bought nothing from us at all. Give us that month and its count of unique visitors, and when a few months run neck and neck, the earliest is the one worth digging into first.

Everyone showed up. Nobody bought anything.

Tables

user_sessions

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

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this problem is really asking

This is a global anti-join hiding inside a seasonal-analytics question. The real skill: express 'never appears in transactions' as a set difference over the WHOLE table, bucket sessions by calendar month, and count the distinct people in each. Three traps decide it. Scope the purchase check to the session's own month and you brand a shopper who bought in a different month as non-converting. Reach for COUNT(*) over COUNT(DISTINCT us.user_id) and one visitor with several sessions counts many times. Leave the tie unbroken and the engine hands back whichever of the four top months it feels like. Here COUNT(*) even crowns '2024-12' (eight sessions from five repeat visitors) over the honest winner '2026-01'.

Working through it

Step 1: Keep only visitors who never bought, table-wide

us.user_id NOT IN (SELECT user_id FROM transactions) keeps sessions whose visitor appears nowhere in transactions. The subquery is unscoped by date on purpose: any purchase we have ever recorded makes someone a buyer, so a month-matched slice would wrongly spare cross-month buyers.

Step 2: Drop sessions with no recognized device

us.device_id IS NOT NULL removes sessions that carry no recognized device before anything is counted. Skip it and those sessions inflate the buckets and shift the peak to '2025-01'.

Step 3: Bucket by calendar month and count distinct visitors

strftime('%Y-%m', us.session_start) groups each session into its year-and-month bucket as session_month. COUNT(DISTINCT us.user_id) then counts people, not rows: a visitor with three sessions in one month is one non_converting_users, not three.

Step 4: Surface the single peak, deterministically

ORDER BY non_converting_users DESC, session_month ASC with LIMIT 1 returns the busiest bucket. Four months tie at the top on this data, so the session_month ASC tiebreak is what makes '2026-01' the deterministic winner rather than whichever tied row the engine surfaces first.

The solution

SELECT strftime('%Y-%m', us.session_start) AS session_month, COUNT(DISTINCT us.user_id) AS non_converting_users FROM user_sessions us WHERE us.user_id NOT IN (SELECT user_id FROM transactions) AND us.device_id IS NOT NULL GROUP BY session_month ORDER BY non_converting_users DESC, session_month ASC LIMIT 1

Common follow-up questions

• Why is `NOT EXISTS` usually preferable to `NOT IN` at this scale? (Tests planner intuition: `NOT EXISTS` correlates per outer row and streams, while `NOT IN` must materialize the whole subquery result first.)
• How would you report the peak calendar month pooled across every year instead of a single year-month? (Switches the bucket to `strftime('%m', session_start)`, pooling all Januaries, and changes whether `COUNT(DISTINCT ...)` still differs from `COUNT(*)`.)
• What breaks if `transactions.user_id` can be `NULL`? (A single `NULL` in the subquery makes `NOT IN` return no rows through three-valued logic, so the buyer set needs an `IS NOT NULL` guard or a switch to `NOT EXISTS`.)