I have an input table as below:

My expected output is:

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.

CodePudding user response：

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;