High Engagement Pages

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

L3

Asked in

Meta

Problem

We run a web analytics product where every page view logs how long it stayed on screen, in milliseconds, and which user viewed it. For each session a user starts, add up the on-screen time in seconds across all of that user's page views.

Some pages hold attention longer than others.

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 problem is really asking

Beneath the analytics framing this is a cross-table sum: the value you group by, session_id, lives in user_sessions, while the thing you add up, dur_ms, lives in page_views, and the only column the two tables share is user_id. So the real question is how a page view maps to a session, and the honest answer is that it does not map to one directly. A page view is stamped with a user, not a session, so a session's viewing time is the total on-screen time of the user who owns that session, and every session that user started carries the same total.

The one thing to notice

There is no session_id on page_views. A candidate who goes hunting for one, or who tries to line a view up with a single session, stalls. The link is user_id: join user_sessions to page_views on it and every session a user started collects that user's whole set of views. That repetition is the intended grain here, not a bug to design around.

Building the query

Step 1: Join the two tables on the shared user

INNER JOIN page_views pv ON pv.user_id = us.user_id attaches every one of a user's views to each of that user's sessions. Because it is an inner join, a session whose user logged no views quietly drops out, which is exactly the membership you want: a session only counts once there is on-screen time behind it.

Step 2: Total the on-screen time per session in seconds

Group by us.session_id and compute SUM(pv.dur_ms) / 1000.0. Summing the raw millisecond durations and dividing once at the end gives seconds; the / 1000.0 keeps the result a real number instead of integer-truncating. The roughly 1% of dur_ms that are NULL are ignored by SUM, which is the behavior you want.

The solution

SELECT us.session_id, SUM(pv.dur_ms) / 1000.0 AS total_seconds
FROM user_sessions us
INNER JOIN page_views pv ON pv.user_id = us.user_id
GROUP BY us.session_id

Common follow-up questions

• How would you report each user's total viewing time exactly once, rather than repeated across every session they started? (Tests whether the candidate understands the user-to-session grain and can avoid double counting.)
• About 1% of `dur_ms` values are `NULL`. What does `SUM(dur_ms)` do with them, and when would that be the wrong choice? (Tests understanding of how NULLs behave inside aggregate functions.)
• What changes in the result if you switch the `INNER JOIN` to a `LEFT JOIN` from `user_sessions`? (Tests awareness of inner versus outer join semantics on the membership edge.)