Indeed. There is no metadata tab. It is really annoying that it is nearly impossible to find your own queries in this. Also the filtering options are not great. ... View more

Sorry, pasted the wrong output: +---------------+-----------+----------------------------------------------------+ | customer | mywindow | row_count_list | +---------------+-----------+----------------------------------------------------+ | somecustomer | 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] | | somecustomer | 2 | [46,47] | | somecustomer | 3 | [48,49] | | somecustomer | 4 | [51,50] | | somecustomer | 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] | | somecustomer | 6 | [108] | | somecustomer | 7 | [109] | | somecustomer | 8 | [110] | +---------------+-----------+----------------------------------------------------+ ... View more

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