Skip to content
Advertisement

Combine rows by consecutive timestamp

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:

  1. 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.
  2. 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;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement