sql - Get range between FIRST_VALUE and LAST_VALUE

Question

timestamp	id	scope
2021-01-23 12:52:34.159999 UTC	1	enter_page
2021-01-23 12:53:02.342 UTC	1	view_product
2021-01-23 12:53:02.675 UTC	1	checkout
2021-01-23 12:53:04.342 UTC	1	search_page
2021-01-23 12:53:24.513 UTC	1	checkout

question from:https://stackoverflow.com/questions/65942736/get-range-between-first-value-and-last-value

Answer 1

You can do it with GROUP_CONCAT() window function which supports the ORDER BY clause so you will have the scopes in inbetween_pages in the correct order, instead of GROUP_CONCAT() aggregate function which does not support the ORDER BY clause and the results that it returns are not guaranteed to be in a specific order:

SELECT DISTINCT id, first_page, last_page,
       GROUP_CONCAT(CASE WHEN timestamp NOT IN (min_timestamp, max_timestamp) THEN scope END) 
       OVER (PARTITION BY id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) inbetween_pages
FROM (
  SELECT *,
         FIRST_VALUE(scope) OVER (PARTITION BY id ORDER BY timestamp) first_page,
         FIRST_VALUE(scope) OVER (PARTITION BY id ORDER BY timestamp DESC) last_page,
         MIN(timestamp) OVER (PARTITION BY id) min_timestamp,
         MAX(timestamp) OVER (PARTITION BY id) max_timestamp
  FROM tablename       
)

See the demo.
Results:

id	first_page	last_page	inbetween_pages
1	enter_page	checkout	view_product,checkout,search_page