Completed Sessions — IS NULL in SQL

The problem

Streamhub's billing team processes charges on sessions that have completed (and therefore have a recorded end time).

Write a query to return the ID, user ID, and start time of every session that has ended.

Assumptions:

The sessions table contains every session ever recorded on the Streamhub platform.
The ended_at column records when each session closed; sessions still in progress have ended_at set to NULL and should be excluded.
The user_id column links each session to the user who initiated it.

Output:

One row per completed session, with columns id, user_id, and started_at.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  user_id,
  started_at
FROM
  sessions
WHERE
  ended_at IS NOT NULL

The shape

"Completed" is encoded as the presence of an end time, not a boolean flag. The billing run needs every session whose ended_at has been recorded, so IS NOT NULL on that column is the completed-session filter.

Clause by clause

SELECT id, user_id, started_at returns the three columns billing needs: which session, which user to charge, and when the session began.
FROM sessions reads the session log. The same table that holds open sessions (with ended_at as NULL) holds the closed ones too; the difference is only in the value of that one column.
WHERE ended_at IS NOT NULL keeps only rows where the end timestamp is present. IS NOT NULL returns true for completed sessions and false for in-progress ones.

Why this and not a boolean `is_complete` flag

Some schemas track completion with a boolean column. This one uses the NULL-ness of ended_at as the same signal, and it's tighter: a single column holds both the status (open or closed) and the data (when it closed). A boolean flag plus an end-timestamp column would let the two drift out of sync: is_complete = true with ended_at = NULL, or vice versa. With this shape, the truth lives in one place.

The trap

Billing on WHERE ended_at <> NULL would return zero rows and charge no one, even though dozens of sessions completed. <> against NULL evaluates to unknown, never true, so the filter passes nothing. The query runs without error, the billing report comes back empty, and revenue silently disappears. Any operator that compares to NULL (=, <>, <, >) produces unknown. IS NOT NULL is the only correct test for presence.

You practiced using IS NOT NULL to scope a query to records that have reached a particular state. End-timestamp-not-null is the everyday shape for 'completed only' filters.

Return the ID, user ID, and start time of every session that has ended

The shape

Clause by clause

Why this and not a boolean `is_complete` flag

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not a boolean is_complete flag

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not a boolean `is_complete` flag