Sessions with Account Plans — INNER JOIN in SQL

The problem

Streamhub's data team is auditing session-level activity and needs to associate each session record with the plan tier of the account that generated it.

Write a query to return the session ID and account plan for every session on record.

Assumptions:

The sessions table contains every session ever recorded; user_id on each session points to a row in users.
The users table contains every account on the platform, identified by users.id.
Every session has a valid user_id, so every session will appear in the result.

Output:

One row per session, with columns session_id and account_plan.

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
  s.id AS session_id,
  u.plan AS account_plan
FROM
  sessions s
  JOIN users u ON s.user_id = u.id

The shape

The sessions table records per-event activity but doesn't carry the account's plan tier. Joining to users on sessions.user_id = users.id brings the plan column into scope, so each session row in the result picks up the plan attribute of the account that generated it.

Clause by clause

FROM sessions s reads from the high-volume side: every session ever recorded, one row per session. The alias s keeps later references short.
JOIN users u ON s.user_id = u.id pairs each session with the user account that generated it. The join key is the standard foreign-key shape: s.user_id on the session side, u.id on the users side. Because every session has a valid user_id, the row count of the result matches the row count of sessions. No sessions drop.
SELECT s.id AS session_id, u.plan AS account_plan picks one column from each side: the session's own id (qualified as s.id because both tables have an id column), and the plan attribute from the user record. Both output aliases label the columns by their analytical role rather than their source name. session_id is more readable than a bare id; account_plan makes clear which plan the column is reporting on.

Why this and not store the plan on the session row

The session row deliberately doesn't carry the plan. Plans change — a user on pro last month might be on business this month — and storing the plan on every session would force every plan change to update millions of historical sessions. The standard design is to store the plan once on the user record and pull it through the join whenever a report needs it. The join is how the report joins the dimension to the fact at read time, instead of duplicating it at write time.

You practiced enriching a high-volume fact table (sessions) with a low-volume dimension column (plan). The recurring shape any time a per-event report needs context from the entity that produced the event.

Return the session ID and account plan for every session on record

The shape

Clause by clause

Why this and not store the plan on the session row

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