Session Count per User — CTEs in SQL

The problem

Streamhub's product analytics team tracks how frequently users engage with the platform.

Write a query to return each user's ID and the number of sessions they have recorded.

Assumptions:

The sessions table has one row per session with a user_id.
Each user with at least one session should appear once in the report.

Output:

One row per user, with columns user_id and session_count.

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

WITH
  user_sessions AS (
    SELECT
      user_id,
      COUNT(*) AS session_count
    FROM
      sessions
    GROUP BY
      user_id
  )
SELECT
  user_id,
  session_count
FROM
  user_sessions

The shape

A single WITH layer aggregates sessions by user_id, and the main query reads the per-user count straight out of it. The layer's name, user_sessions, behaves like a table reference in the main FROM.

Clause by clause

The WITH clause defines user_sessions:

WITH user_sessions AS (
  SELECT user_id, COUNT(*) AS session_count
  FROM sessions
  GROUP BY user_id
)

GROUP BY user_id partitions sessions into one bucket per user, and COUNT(*) counts the rows in each bucket. The result is one row per user with their session count: user 1 ends up with 9, user 14 with 4, user 5 with 1, and so on across every user who has at least one recorded session.

SELECT user_id, session_count FROM user_sessions is the main query. It reads from the named layer like any other table source and returns both of its columns. No additional logic runs in the main query; the work already happened inside the layer.

You practiced naming a per-user breakdown in a WITH layer that the main query reads from.

Return each user's ID and the number of sessions they have recorded

The shape

Clause by clause

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