Streamhub's data engineering team wants a scaffold for per-period session analytics: one row per user session per reporting period. Each existing session must be expanded across all four quarterly periods.
Write a query to return the user ID, session ID, and period name for every (session × period) combination.
Assumptions:
- There are exactly four reporting quarters.
- The result combines two operations: each session is matched to its user (every session has exactly one user), and each session is expanded across all four quarterly periods.
Output:
- One row per session-period combination, with columns
user_id,session_id, andperiod_name.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
u.id AS user_id,
s.id AS session_id,
p.name AS period_name
FROM
users u
CROSS JOIN periods p
JOIN sessions s ON s.user_id = u.idThe shape
Two joins do two different jobs in the same query. JOIN sessions s ON s.user_id = u.id matches each user to their actual sessions — a real relationship in the data. CROSS JOIN periods p then expands the result across all four quarters, unconditionally. The output is one row per existing session per quarter — the scaffold the data engineering team needs for per-period analytics.
Clause by clause
FROM users u CROSS JOIN periods pproduces the user-by-period grid first: every user paired with every quarter, no condition.JOIN sessions s ON s.user_id = u.idthen matches that intermediate result againstsessionson the user-ID link. Each session has exactly one user, so a session attached to user1joins onto the four(user 1, period)rows already in the intermediate result. Every session ends up appearing four times — once paired with each quarter.u.id AS user_idreads the user ID from the users side.s.id AS session_idreads the session ID.p.name AS period_namereads the quarter label. Three columns, three source tables, one per column.
Why this and not sessions CROSS JOIN periods
SELECT s.user_id, s.id AS session_id, p.name FROM sessions s CROSS JOIN periods p would return the same rows in this dataset, because sessions.user_id already carries the link to users and you don't need to materialise the users row to read the session-period grid. The query in front of you keeps users in the FROM because the scaffold's user ID is being read from u.id rather than s.user_id — same value either way, different source column. Both shapes work; the longer one makes the role of the user table explicit.
The trap
The trap is mentally collapsing the two joins into one and trying to relate sessions to periods directly. JOIN periods p ON ... would require a match condition between sessions and periods, and there is no such column — sessions don't carry a period reference. The relationship the report needs is not a match; it's a complete expansion. CROSS JOIN does that expansion without any condition, and the INNER JOIN to sessions then attaches the real-data side. Two different join types in the same query because two different jobs are happening: matching where data already links, expanding where it doesn't.
You practiced combining CROSS JOIN and INNER JOIN in the same query. Each does its own job — matching on a condition for one, producing every pairing for the other — and they compose freely in the same FROM clause.
