Event Types Array per Session — STRING_AGG and ARRAY

The problem

Streamhub's product analytics team wants every event type recorded in each session as a structured collection rather than flattened into a string.

Write a query to return every session ID and an array of all event types that occurred in that session, with the array elements arranged in alphabetical order.

Assumptions:

The events table has one row per event with a session_id and an event_type.
Each session_id with at least one event should appear once in the result.
For each session, the array contains every event_type value across that session's events (one element per event, no de-duplication), arranged alphabetically.

Output:

One row per session with at least one event, with columns session_id and event_types.

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
  session_id,
  ARRAY_AGG(
    event_type
    ORDER BY
      event_type
  ) AS event_types
FROM
  events
GROUP BY
  session_id

The shape

ARRAY_AGG(event_type ORDER BY event_type) packages every event in a session into a single typed array, with the elements arranged alphabetically inside the array. The product team gets one row per session and a structured collection they can hand to downstream code instead of a flattened string.

Clause by clause

SELECT session_id, ARRAY_AGG(event_type ORDER BY event_type) AS event_types returns the session and its array of event types. ARRAY_AGG collects the values into a PostgreSQL array; the result column carries a text-array type because the input expression is text. The ORDER BY event_type inside the aggregate fixes the element sequence, which is why session 4 reads ['checkout', 'feature_used', 'feature_used', 'page_view', 'purchase', 'upgrade_clicked'] rather than an unpredictable order.
FROM events reads the event rows. Every event in the table contributes.
GROUP BY session_id partitions the rows so the aggregate runs once per session. One output row comes out for each distinct session_id.

Why this and not `STRING_AGG`

STRING_AGG would also collect the values, but it would flatten them into a comma-separated text value. The prompt asks for a structured collection so downstream code can iterate, index, or unnest. ARRAY_AGG keeps the values typed and addressable; STRING_AGG would force a re-parse downstream.

You practiced ARRAY_AGG(column ORDER BY column) — collect grouped values into a typed PostgreSQL array; downstream code can index, unnest, or compare elements directly.

Return every session ID and an array of all event types that occurred in that session, with the array elements arranged in alphabetical order

The shape

Clause by clause

Why this and not `STRING_AGG`

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 STRING_AGG

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

Why this and not `STRING_AGG`