Activity Histogram

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
L3

Problem

A user's activity level is the number of sessions they have in user_sessions, and a session with no device recorded still counts toward it. Build the histogram of those levels, showing how many users land on each one, lowest level first.

How many users did X things? Build the distribution.

Tables

user_sessions

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

user_sessions: 10 of 200 rows
session_iduser_iddevice_idsession_startsession_duration_secpages_viewed
70611972302026-02-02 01:00:00533
71222942592026-03-03 02:00:00766
71833912882026-04-04 03:00:00999
72444883172026-05-05 04:00:0012212
73055853462026-06-06 05:00:0014515
73666823752026-07-07 06:00:0016818
74277794042026-08-08 07:00:0019121
7488876NULL2026-09-09 08:00:0021424
75499734622026-01-10 09:00:0023727
761010704912026-02-11 10:00:0026030

Expected output

First 5 of 5 rows
session_countuser_count
19
22
328
519
81

Worked solution and explanation

This is a histogram of a histogram dressed up as retention research. Everyone writes the first pass: GROUP BY user_id with COUNT(*), one row per user. The skill being probed is noticing that this is only the input. The question asks how many users share each count, so the count you just computed has to become the grouping key of a second pass. Stop after one pass and you hand back 59 rows of user ids instead of five bars, and the shape the team wanted to see never appears.

Two passes, two grains

Step 1: Collapse sessions to one row per user

GROUP BY user_id with COUNT(*) turns 200 sessions into 59 users, each carrying a session_count. Count rows, not a column: 23 sessions have a NULL device_id, so COUNT(device_id) would quietly push some users down a level.

Step 2: Group on the count you just computed

In the second pass session_count stops being a measure and becomes the key. The outer GROUP BY session_count with COUNT(*) counts users per level: 9 users at one session, 28 at three, 19 at five, a single user at eight.

Step 3: Read it lowest level first

Sort by session_count ascending so the bars run left to right. Levels nobody reached (4, 6, 7) simply do not appear: a histogram of observed levels has no zero rows unless you generate them yourself.

Per-user counts, then users per count
WITH user_counts AS (
    SELECT user_id, COUNT(*) AS session_count
    FROM user_sessions
    GROUP BY user_id
)
SELECT session_count, COUNT(*) AS user_count
FROM user_counts
GROUP BY session_count
ORDER BY session_count

user_counts is the first grain (one row per user); the outer query is the second (one row per level).

One pass
SELECT user_id, COUNT(*) FROM user_sessions GROUP BY user_id returns 59 rows keyed by user_id. The numbers are right but they answer the wrong question: nobody can read a distribution off a list of users.
Two passes
Feed that result into GROUP BY session_count and the 59 rows collapse into 5 bars, one per level some user actually reached, with user_count as the bar height.

Common follow-up questions