I have an input table as below:
name | time | price |
---|---|---|
one | 2022-11-22 19:00:00 UTC | 12 |
one | 2022-11-23 7:00:00 UTC | 24 |
one | 2022-11-23 19:00:00 UTC | 10 |
one | 2022-11-24 7:00:00 UTC | 20 |
My expected output is:
name | time | price |
---|---|---|
one | 2022-11-22 | 36 |
one | 2022-11-23 | 30 |
Explanation:
- I have to group-by 2 consecutive timestamps, the prev date 19:00:00 UTC and the next date 7:00:00 UTC and name the row with the prev date.
- Sum the price for each 2 consecutive rows.
Approach: As I understand, I have to use partition by on the time column, but I cannot figure out how to combine with exact timestamps.
Advertisement
Answer
with cte as ( select name, time, price, (row_number() over (partition by name order by time)+1) div 2 as group_no from consec_data) select name, min(time) as time, sum(price) as price from cte group by name, group_no;