For me (working on CDP) this is still now working. create temporary table stage1 as SELECT * FROM ( SELECT customer , ts , sum(enodmywindow) over (partition by customer order by ts desc rows between unbounded preceding and current row) mywindow , sum(1) over (partition by customer order by ts desc rows between unbounded preceding and current row) row_count FROM od_stage1 WHERE previous_day_ts is null -- to change when the next day ts is fixed ) a DISTRIBUTE BY customer, mywindow sort by customer, mywindow, row_count ; create temporary table stage2 as SELECT customer , mywindow , collect_list(ts) ts_list , collect_list(row_count) row_count_list FROM (select * from stage1 DISTRIBUTE BY customer, mywindow sort by customer, mywindow, row_count ) x group by customer , mywindow ; +-------------------+--------------------------+-------------+----------------------------------------------------+ | customer | trip_count | row_count_list | +-------------------+--------------------------+-------------+----------------------------------------------------+ | someone | 2023-01-23 17:18:30.89 | 8 | [110] | | someone | 2023-01-24 01:34:19.052 | 7 | [109] | | someone | 2023-01-24 03:53:09.388 | 6 | [108] | | someone | 2023-01-24 09:32:03.172 | 5 | [71,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107] | | someone | 2023-01-24 11:48:21.606 | 4 | [51,50] | | someone | 2023-01-24 12:29:52.992 | 3 | [48,49] | | someone | 2023-01-24 17:35:35.846 | 2 | [46,47] | | someone | 2023-01-24 20:03:30.239 | 1 | [20,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45] | +-------------------+--------------------------+-------------+----------------------------------------------------+
... View more
