Session Count Distribution

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

L4

Asked in

Meta

Problem

Among the users who signed up in 2024 or 2025, how many logged exactly one session in user_sessions during 2026, how many logged exactly two, and so on up the scale? The breakdown reads from the smallest session count to the largest.

How are sessions distributed among the newest users?

Tables

users

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

user_sessions

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

Expected output

Worked solution and explanation

This is a count of counts

Strip off the analytics costume and this is a two-level aggregation: a distribution of a value that does not exist yet. The bucket key is each user's own session count, and no column holds it until you have already grouped once. Anyone can count sessions per user. The move that separates candidates is realizing you then have to group AGAIN, this time by that computed count. Miss it and you write one flat grouping that answers a different question. The second trap is quieter: apply the signup window loosely and users who joined outside 2024 and 2025 keep contributing, quietly inflating every bucket.

Build it in stages

Step 1: Fence the cohort first

Restrict users to the cohort with signup_date BETWEEN '2024-01-01' AND '2025-12-31'. This is not decoration: on this data, users who joined outside those two years still logged sessions in 2026, so skipping it pushes the one-session bucket from 7 up to 11. Apply it before you count, not after.

Step 2: Collapse to one row per user

Join the cohort to user_sessions, keep only rows where strftime('%Y', session_start) = '2026', and GROUP BY user_id with COUNT(*). Now every qualifying user is one row holding their 2026 session total. Users with zero 2026 sessions simply never appear here, which is exactly why no 0 bucket is fabricated later.

Step 3: Aggregate the counts themselves

Take that per-user result and GROUP BY session_count, counting the users at each value. Order ascending so the distribution reads from the quietest users to the most engaged.

WITH cohort AS (
    SELECT user_id
    FROM users
    WHERE signup_date BETWEEN '2024-01-01' AND '2025-12-31'
),
sessions_per_user AS (
    SELECT s.user_id, COUNT(*) AS session_count
    FROM user_sessions s
    INNER JOIN cohort c ON s.user_id = c.user_id
    WHERE strftime('%Y', s.session_start) = '2026'
    GROUP BY s.user_id
)
SELECT session_count, COUNT(*) AS user_count
FROM sessions_per_user
GROUP BY session_count
ORDER BY session_count ASC

One flat grouping (wrong)

GROUP BY user_id, COUNT(*) and stop. That gives you a per-user list, not a distribution. Or worse, GROUP BY some session column and lose the per-user step entirely. Either way you never answer 'how many users had exactly N sessions'.

Count of counts (right)

Group once to get each user's count, then group that result by the count value. The distribution's key is manufactured by the first aggregation and consumed by the second.

Common follow-up questions

• Product now wants users with zero sessions counted as their own bucket. How does the query change? (Tests whether the candidate reaches for a LEFT JOIN from the cohort and understands it is a deliberate change, not a bug fix.)
• How would you collapse the tail into ranges, say 1, 2 to 4, and 5 or more, instead of exact counts? (Tests understanding that the histogram can be bucketed a second time (ranges of counts) without re-scanning sessions.)
• If one user had thousands of sessions, how would that show up here, and would it distort the story you are telling? (Tests reasoning about skew: a few very active users stretch the distribution and dominate the high buckets.)