Unique Visitors

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

List the calendar months in user_sessions where someone came back, showing each month's number of different users next to its number of sessions. An analyst auditing the board's monthly active users report wants only the months where at least one visitor started more than one session, since those are the months where raw activity overstates the audience.

Which months actually had an audience?

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 10 rows
activity_monthunique_visitorssession_count
2023-0158
2023-0557
2023-0957
2024-0457
2026-061517

Worked solution and explanation

What this really is

This is a people-versus-events question wearing a board-report costume. Everyone buckets session_start into months and counts something. What separates candidates is holding two different counts of the same rows at once: sessions and the people behind them. The months the analyst wants are exactly the ones where those numbers disagree, so a query that counts rows on both sides compares a number with itself and returns nothing.

Step 1: Collapse timestamps to a calendar month

STRFTIME('%Y-%m', session_start) turns every session into its year-month label, so March 2025 and March 2026 stay separate months. Group by that expression, not by the raw timestamp, or every session becomes its own group.

Step 2: Count people and sessions side by side

COUNT(DISTINCT user_id) is the audience; COUNT(*) is the activity. Return both, because the gap between them is the whole story the report needs to see.

Step 3: Keep months where activity outran the audience

A month has a returning visitor exactly when it has more sessions than different users. That comparison is about the grouped result, so it lives in HAVING COUNT(*) > COUNT(DISTINCT user_id). Drop the DISTINCT there and the condition can never be true.

The solution

Months where somebody came back
SELECT
    STRFTIME('%Y-%m', session_start) AS activity_month,
    COUNT(DISTINCT user_id) AS unique_visitors,
    COUNT(*) AS session_count
FROM user_sessions
GROUP BY STRFTIME('%Y-%m', session_start)
HAVING COUNT(*) > COUNT(DISTINCT user_id)
Counts rows twice
HAVING COUNT(*) > COUNT(user_id) compares session rows with session rows. user_id is never empty, so the two are always equal and every month is dropped.
Counts people once
HAVING COUNT(*) > COUNT(DISTINCT user_id) counts each person once per month, so it is true exactly when someone opened a second session that month.

Common follow-up questions