Content Session Counts

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Meta

Problem

The content team's session-engagement report shows '/Home' and '/home' as two separate pages, because page_views logs the same URL in more than one letter case. Rebuild it with each page counted once regardless of case, showing how many unique sessions sit behind it, most sessions first. A view records only its user and never its session, so a page is credited with every session in user_sessions belonging to a user who viewed it.

Session metrics, content item by item.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at 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

What this really is

This is a many-to-many fan-out in a content-analytics costume, with a dirty grouping key on top. page_views has no session_id, so the only road to sessions is user_id, and that key repeats on both sides of the join. Anyone can write the join. What separates candidates is what they count after it: COUNT(*) multiplies every session by each view its user left on the page, so tens of sessions turn into hundreds and the order tilts toward heavy viewers. The second trap is quieter. '/Home' and '/home' are one page, and a candidate who fixes the case after counting double-counts every session that reached both spellings.

Step 1: Normalize the page before you group

page_url holds '/Home' and '/home', '/PRODUCTS' and '/products'. Group on LOWER(pv.page_url) so each page lands in one bucket before anything is counted. Lowercasing only the output column is too late, because the groups have already split.

Step 2: Bridge through user_id

Inner-join user_sessions on user_id. A view records no session and the statement promises no time match, so every session of a viewer is credited to the page. Views with a NULL user_id drop out, which is right, since an anonymous view belongs to nobody's session.

Step 3: Count each session once per page

COUNT(DISTINCT us.session_id) collapses the fan-out. Because both spellings now share a group, a session reached through '/PRODUCTS' and '/products' is counted once, not twice.

Step 4: Sort by the session count

ORDER BY unique_sessions DESC puts the strongest pages on top. Pages tied on a count may come back in any order, and the grader accepts that.

SELECT
    LOWER(pv.page_url) AS page_url,
    COUNT(DISTINCT us.session_id) AS unique_sessions
FROM page_views pv
JOIN user_sessions us ON us.user_id = pv.user_id
GROUP BY LOWER(pv.page_url)
ORDER BY unique_sessions DESC

Summing the spellings afterwards

'/products' has 35 sessions and '/PRODUCTS' has 38, so adding the two finished rows reports 73.

Normalizing before counting

Grouping on LOWER(pv.page_url) lets COUNT(DISTINCT us.session_id) see both spellings at once. Three users viewed both, so the page really has 58 sessions.

SELECT
    LOWER(pv.page_url) AS page_url,
    COUNT(*) AS unique_sessions
FROM page_views pv
JOIN user_sessions us ON us.user_id = pv.user_id
GROUP BY LOWER(pv.page_url)
ORDER BY unique_sessions DESC

Common follow-up questions

• What changes if `page_views` gained a `session_id` column? (The bridge approximation goes away, but you still need unique counting because one session views a page many times.)
• How would you also report average `dur_ms` per page? (Compute it over `page_views` before the join, since the fan-out weights each view by its user's session count.)
• What if a handful of `user_id` values carry most of the traffic? (Tests recognizing join-key skew: pre-aggregating to page-user pairs, salting, or adaptive skew handling.)