Streamhub's product analytics team is measuring user engagement at the session level.
Write a query to return one row per session that contains at least one event, showing the user's ID, name, the session ID, and the total event count for that session.
Assumptions:
- A session's event count is the number of events linked to that session. Sessions with zero events do not appear, and users with no qualifying sessions also do not appear.
Output:
- One row per session with at least one event, with columns
user_id,name,session_id, andevent_count.
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,
u.name,
session_data.session_id,
session_data.event_count
FROM
users u
CROSS JOIN LATERAL (
SELECT
s.id AS session_id,
COUNT(*) AS event_count
FROM
sessions s
JOIN events e ON e.session_id = s.id
WHERE
s.user_id = u.id
GROUP BY
s.id
) session_dataThe shape
The lateral runs per user and groups that user's sessions by session id, returning one row per session that contains at least one event. The outer user row is duplicated once per qualifying session, which is the per-session engagement shape the analytics team needs. Users with no qualifying sessions return zero rows from the lateral and CROSS JOIN LATERAL drops them, which is what the prompt asks for.
Clause by clause
SELECT u.id AS user_id, u.name, session_data.session_id, session_data.event_countreturns the user's identity from the outer table and the per-session result from the lateral.FROM users uis the driving table; the lateral is evaluated once per user.CROSS JOIN LATERAL ( ... ) session_datais the lateral. Inside it,FROM sessions s JOIN events e ON e.session_id = s.idpairs each session with its events,WHERE s.user_id = u.idis the correlated filter that scopes the work to the outer user,GROUP BY s.idcollapses to one row per session, and the innerSELECTreturns the session id and its event count. The inner join drops sessions with zero events, and users with no qualifying sessions get an empty lateral and disappear under theCROSS JOIN.
Why this and not a three-table flat join
FROM users u JOIN sessions s ON ... JOIN events e ON ... GROUP BY u.id, u.name, s.id returns the same rows on this data and is a valid answer. The lateral form maps more cleanly to the question the analytics team is asking: "for each user, summarise each of their sessions." It also scales: if the inner work later needs the most recent event per session via ORDER BY ... LIMIT 1 or a per-session CASE rollup, the lateral already has the per-row scope to express it without restructuring the outer query.
The trap
The u.id reference inside the lateral is the load-bearing detail. PostgreSQL only allows it because of the LATERAL keyword; remove LATERAL and the planner refuses the query because a FROM item cannot reference a sibling FROM item's columns. Whenever the inner subquery needs to use an outer row's value, LATERAL is the keyword that grants that access.
You practiced CROSS JOIN LATERAL over a multi-table per-user breakdown — the lateral returns one row per qualifying session, multiplying the outer user record once per matching session.
