Streamhub's product analytics team is auditing session records for the current reporting day.
Write a query to return each session's ID, user ID, start time, and effective end time.
Assumptions:
- The
sessionstable has one row per session with anid, auser_id, astarted_at, and anended_at. - Sessions still in progress have a missing
ended_at; completed sessions have a recordedended_at. - An in-progress session should appear with an effective end time of
2026-03-22 23:59:59+00; completed sessions should show their recordedended_at.
Output:
- One row per session, with columns
id,user_id,started_at, andended_at.
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
id,
user_id,
started_at,
COALESCE(ended_at, '2026-03-22 23:59:59+00'::TIMESTAMPTZ) AS ended_at
FROM
sessionsThe shape
COALESCE(ended_at, '2026-03-22 23:59:59+00'::timestamptz) substitutes the end-of-day timestamp for any session that has not closed yet, so every row in the audit has a real effective end time to work with. The ::timestamptz cast is what lets the fallback share a type with the column.
Clause by clause
SELECT id, user_id, started_at, COALESCE(ended_at, '2026-03-22 23:59:59+00'::timestamptz) AS ended_atreturns each session's identifying columns plus the effective end time. For completed sessions,COALESCEreturns the recordedended_atand the cast is unused; for in-progress sessions, it falls through to the typed literal.FROM sessionsreads every session. In-progress sessions stay in the result, since those are exactly the rows the fallback is for.
Why the explicit ::timestamptz cast
COALESCE requires its arguments to share a common type, and PostgreSQL resolves a bare string literal as text by default. Passing a raw '2026-03-22 23:59:59+00' would force a timestamptz versus text comparison and either error out or coerce the column to text. Writing '2026-03-22 23:59:59+00'::timestamptz declares the literal's type up front, so both COALESCE arguments are timestamptz and the result column comes out as timestamptz too, which is what any downstream duration calculation needs.
You practiced COALESCE against a typed literal — substitute a domain-specific timestamp for a missing one so a downstream calculation always has a value.
