Every Door They Opened

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

Asked in

Spotify

Problem

How many minutes of session time in user_sessions, rounded to the nearest minute, and how many different pages in page_views has each user logged since 2026 began? The analyst wants a row for everyone who has ever appeared in either log, so a user whose activity all predates 2026 still shows up with zeros, while page views with no user id are anonymous traffic that belongs to no one. Page addresses that differ only in letter case are the same page.

Add up the hours, count the rooms they walked through.

Tables

user_sessions

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

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

Worked solution and explanation

The disguise and the trap

This is a two-source reconciliation wearing an engagement-report costume, and the twist is that the roster and the numbers live on different clocks. The roster is everyone who has ever appeared in either log; the metrics only count activity since 2026 began. Join the raw tables and a user with 10 sessions and 100 page views explodes into 1,000 rows, so the duration sum lands 100x high and the page count 10x high. Anchor on one table, or let the date filter leak into the roster, and every dormant user and every view-only user quietly vanishes. The fix: build the roster from the union of both logs, collapse each log to one row per user since the start of the year, then left join and zero-fill.

Break down the requirements

Step 1: Build the full user roster

Union user_id from all of user_sessions and all of page_views, with no date filter: the roster is who has ever shown up, not who was active this year. UNION removes duplicates, so each user lands once. Only the page-view branch needs user_id IS NOT NULL: that log carries anonymous traffic, and without the guard the roster grows a phantom user whose id is missing.

Step 2: Sum session duration per user

SUM(session_duration_sec) over sessions with session_start on or after the first of 2026, grouped by user_id, then ROUND(total_secs / 60.0). Divide by 60.0 rather than 60 so rounding acts on the exact value. The window lives here and not on the roster: a user whose sessions are all from earlier years still belongs in the report, they just have no row on this side.

Step 3: Count unique pages per user, case folded

COUNT(DISTINCT LOWER(page_url)) over page views since the start of 2026, grouped by user_id. The uniqueness is load-bearing: the same URL opened five times is one page, not five. So is the case fold: the log writes '/Home' next to '/home' and '/PRODUCTS' next to '/products', and a raw count credits those readers with a page they never saw. Forget the window on this side and long-time readers get credit for pages they opened years ago.

Step 4: Left join both aggregates and zero-fill

Left join both yearly sides onto the roster and COALESCE each metric to 0. A user dormant all year has no session row and no page row, and shows 0 and 0 instead of disappearing. An inner join would keep only users active on both sides this year.

The solution

SELECT
    roster.user_id,
    COALESCE(ROUND(sessions_this_year.total_secs / 60.0), 0) AS total_minutes,
    COALESCE(views_this_year.unique_pages, 0) AS unique_content_count
FROM (
    SELECT user_id
    FROM user_sessions
    UNION
    SELECT user_id
    FROM page_views
    WHERE user_id IS NOT NULL
) AS roster
LEFT JOIN (
    SELECT user_id, SUM(session_duration_sec) AS total_secs
    FROM user_sessions
    WHERE session_start >= '2026-01-01'
    GROUP BY user_id
) AS sessions_this_year
    ON roster.user_id = sessions_this_year.user_id
LEFT JOIN (
    SELECT user_id, COUNT(DISTINCT LOWER(page_url)) AS unique_pages
    FROM page_views
    WHERE viewed_at >= '2026-01-01'
    GROUP BY user_id
) AS views_this_year
    ON roster.user_id = views_this_year.user_id

Common follow-up questions

• What if `session_duration_sec` contains NULL values? (SUM ignores NULLs silently, so the total drops with no error. COALESCE each value to 0 inside the SUM, or confirm with the interviewer that skipping nulls is acceptable.)
• How would you add each user's single most-viewed page alongside these totals? (Tests whether the candidate can layer a per-user top-N pick onto the existing aggregates without breaking the one-row-per-user shape.)
• What if a content item is identified by `content_id` rather than `page_url`? (Tests whether the candidate separates the identity of a content item from its URL. If a `content_id` column exists, COUNT DISTINCT on it; otherwise join to a content mapping table.)