CODEWITHSUNDEEP
snowflake-cloud-data-platform
SmokeMIfYouGotM
SmokeMIfYouGotM

Reputation: 11

Concat rollup values

I have a table with a column of dates.

emp_id,some_date
1,2002-11-23
1,2006-06-09
1,2009-05-05
1,2013-06-06
2,1978-07-05
2,1980-04-15
2,1984-08-31

I need my output to look like this

1 2002-11-23
1 2002-11-23,2006-06-09
1 2002-11-23,2006-06-09,2009-05-05
1 2002-11-23,2006-06-09,2009-05-05,2013-06-09
2 1978-07-05
2 1978-07-05,2,1980-04-15
2 1978-07-05,2,1980-04-15,984-08-31

the "rollup" should occur during an active period when the last value of some_date is in the period for example 2002-11-23,2006-06-09 would be the row returned when some_date is between 2006-06-09 and 009-05-04

Upvotes: 1

Views: 173

Answers (1)

Rajat
Rajat

Reputation: 5803

You can array_agg it by emp_id and then slice it later by utilizing dense_rank()

with cte (emp_id, emp_date) as

(select 1,'2002-11-23' union all
 select 1,'2006-06-09' union all
 select 1,'2009-05-05' union all
 select 1,'2013-06-06' union all
 select 2,'1978-07-05' union all
 select 2,'1980-04-15' union all
 select 2,'1984-08-31')

select *,array_slice(array_agg(distinct emp_date) within group (order by emp_date) over (partition by emp_id),
                     0,
                     dense_rank() over (partition by emp_id order by emp_date)) as emp_date_array
from cte
order by emp_id;

Upvotes: 2

Related Questions