How to find the next group value using Big query SQL

Question

I have a table with following values.

Order Id    Order_start_date
O1          9/10/2022
O1          9/10/2022
O1          9/12/2023
O1          9/12/2023
O1          9/14/2023
O1          9/14/2023

I want to get next_order_start_dt as which is next group start date. So Result I am expecting as

Order_id    Order_start_date    Next_Order_start_date
O1          9/10/2022           9/12/2023
O1          9/10/2022           9/12/2023
O1          9/12/2023           9/14/2023
O1          9/12/2023           9/14/2023
O1          9/14/2023           Null
O1          9/14/2023           Null

Here is my Table SQL

WITH  ORDER_DETAIL AS 
(
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
)
SELECT ORD_ID, ORDER_START_DT
FROM ORDER_DETAIL

How will I get the Next_order_start_dt using Big query SQL.

Answer 1

You can consider below.

SELECT ORD_ID, ORDER_START_DT,
       FIRST_VALUE(ORDER_START_DT) OVER w1 AS Next_Order_start_date
  FROM (
    SELECT *, DENSE_RANK() OVER w0 AS rnk
      FROM ORDER_DETAIL
    WINDOW w0 AS (PARTITION BY ORD_ID ORDER BY ORDER_START_DT)
  ) WINDOW w1 AS (PARTITION BY ORD_ID ORDER BY rnk RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING);

• RANGE window frame

Query result