# Every Door They Opened > Add up the hours, count the rooms they walked through. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem We keep two independent logs for the web product: one row per session in the session log and one row per page view in the page-view log, and a single person piles up many of each. For everyone carrying a real user id in either log, report their total time across all sessions rounded to the nearest minute together with how many different pages they opened. ## Worked solution and explanation ### The disguise and the trap This is a two-source reconciliation wearing an engagement-dashboard costume. The skill being probed: can you assemble one row per user across everyone who shows up in either log, pairing a duration sum from user_sessions with a unique-page count from page_views, without letting the two contaminate each other. There are two ways to fail. 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 too high and the page count 10x too high. Anchor the whole thing on one table, or hang a tidy correlated subquery off user_sessions, and every user who only ever viewed pages quietly vanishes. The fix is the same in both cases: aggregate each table to one row per user, build the roster from the union of both, then join. --- ### Break down the requirements #### Step 1: Build the full user roster UNION the user_id from user_sessions with the user_id from page_views. UNION deduplicates, so you get exactly one row per user who appears in either log, including the session-only and view-only users that a single-table anchor would drop. A WHERE user_id IS NOT NULL on each branch keeps any record without a recorded user id out of the roster, which is why an unfiltered full outer join picks up a spurious null-id group that the expected result does not. #### Step 2: Sum session duration per user `SUM(session_duration_sec)` from `user_sessions` grouped by `user_id`, converted to whole minutes with `ROUND(... / 60.0)`. Divide by 60.0 rather than 60 so the arithmetic stays in floating point before rounding. #### Step 3: Count distinct pages per user `COUNT(DISTINCT page_url)` from `page_views` grouped by `user_id`. DISTINCT is load-bearing: the same URL opened five times is one page, not five. #### Step 4: Left join both aggregates and zero-fill LEFT JOIN both pre-aggregated sides onto the roster, then COALESCE each metric to 0. A session-only user gets 0 pages; a view-only user gets 0 minutes. An inner join, or anchoring on either table alone, would silently drop one of those groups. --- ### The solution **Roster from the union, then join two aggregates** ```sql SELECT u.user_id, COALESCE(ROUND(s.total_secs / 60.0), 0) AS total_minutes, COALESCE(p.unique_content, 0) AS unique_content_count FROM ( SELECT user_id FROM user_sessions WHERE user_id IS NOT NULL UNION SELECT user_id FROM page_views WHERE user_id IS NOT NULL ) u LEFT JOIN ( SELECT user_id, SUM(session_duration_sec) AS total_secs FROM user_sessions GROUP BY user_id ) s ON u.user_id = s.user_id LEFT JOIN ( SELECT user_id, COUNT(DISTINCT page_url) AS unique_content FROM page_views GROUP BY user_id ) p ON u.user_id = p.user_id ``` > **Why the plan stays cheap** > > The two aggregations run once each: 40M sessions collapse to about 3M user rows, and 400M page views collapse to about 8M user rows after the DISTINCT. The union roster and the two joins then operate on those small per-user sets, never the raw 440M rows. That is what keeps the plan affordable at scale. > **Common pitfall** > > The tempting shortcut is to group user_sessions by user_id and drop a correlated subquery in for the page count: SELECT user_id, ROUND(SUM(session_duration_sec)/60.0), (SELECT COUNT(DISTINCT page_url) FROM page_views p WHERE p.user_id = s.user_id) FROM user_sessions s GROUP BY user_id. It returns the right numbers for users who have sessions, but every user who only ever viewed pages is gone, because the roster came from one table. Reconciling two sources means the roster has to come from both. > **Interviewers watch for** > > Two tells separate a careful candidate. First, they build the roster from both tables instead of anchoring on one, and they scope it to records that carry a user id so no phantom null group slips in. Second, they divide by 60.0 and reach for ROUND because the ask says nearest minute; CAST to INTEGER or FLOOR rounds down and quietly under-reports. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/every_door_they_opened) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.