Lists in SQL

Every aggregate so far boils a group down to one number or one string. A session in Website Analytics is several page views, and sometimes the question is about all of them in order: where did the visitor land, what did they see next, did they ever reach pricing? list() keeps the whole group as one value:

select session_id,
       list(path order by view_order) as journey
from pageviews
where session_id <= 3
group by session_id
order by session_id

Session 1 is ['/blog', '/blog', '/features', '/']: one row, four pages, in the order they were viewed. That's the main difference from string_agg, which glues values into text. A list stays a list of separate values you can still work with.

The order by goes inside the parentheses. Leave it out and the items come in whatever order DuckDB read the rows, which can change between runs.

Reading a list

Square brackets build a list and also read from one. Positions count from 1, and negative positions count back from the end:

select ['/', '/blog', '/checkout', '/signup'] as journey,
       journey[1]   as first_page,
       journey[-1]  as last_page,
       journey[2:3] as middle,
       journey[9]   as past_the_end,
       len(journey) as pages

The first page is /, the last is /signup, and the slice [2:3] includes both ends. A position past the end returns NULL rather than an error, and so does position 0.

Searching a list

list_contains(list, value) is true when the value appears anywhere in the list. You can test the list in HAVING, since list(path) is an aggregate like any other:

select session_id,
       list(path order by view_order) as journey
from pageviews
group by session_id
having list_contains(list(path), '/signup')
order by session_id

Four sessions reached /signup. Repeating list(path) in HAVING works, but once you want the list's first item, its length, and a filter on it, build it once in a CTE and use it by name. That's the shape of the exercise.

Two differences from other aggregates

• NULLs stay in. count, sum, and string_agg skip NULLs; list keeps one NULL item for each, so its length is the number of rows.
• Every item has one type. A list of numbers can't hold text. [1, 'a'] fails with Could not convert string 'a' to INT32.

In other databases

PostgreSQL has arrays, with the same 1-based positions: array_agg(path order by view_order) builds one, and '/pricing' = any(journey) searches it. DuckDB accepts array_agg too, as another name for list. BigQuery and Snowflake have arrays as well. MySQL, SQLite, and SQL Server don't; there, the usual stand-ins are a JSON array or a comma-separated string.