Write an SQL query to calculate consecutive order counts for each order type in the ‘Order_batch’ table.

Consecutive order count

Table creation script:

CREATE TABLE ORDER_BATCH (
    Seq INT,
    Order_type VARCHAR(50),
    Batch VARCHAR(50)
);

Data insertion script:

INSERT INTO ORDER_BATCH (Seq, Order_type, Batch) VALUES
(1, 'O1', 'B1'),
(2, 'O1', 'B2'),
(3, 'O1', 'B3'),
(4, 'O2', 'B5'),
(5, 'O2', 'B6'),
(6, 'O3', 'B1'),
(7, 'O4', 'B2'),
(8, 'O5', 'B3'),
(9, 'O1', 'B5'),
(10, 'O1', 'B6'),
(11, 'O6', 'B1'),
(12, 'O7', 'B2'),
(13, 'O8', 'B3'),
(14, 'O1', 'B5'),
(15, 'O1', 'B6');

Data:

Order_batch data

Task: Calculate consecutive order counts

Final Query:

WITH cte AS (
 SELECT Seq, Order_type,
 SUM(CASE WHEN Order_type = prev_Order_type THEN 0 ELSE 1 END) OVER(ORDER BY Seq) AS grp
 FROM (SELECT *, LAG(Order_type) OVER(ORDER BY Seq) AS prev_Order_type FROM Order_batch) s
)
SELECT ROW_NUMBER() OVER() AS Seq, Order_type AS 'Order' ,count(*) AS 'Count'
FROM cte
GROUP BY Order_type, grp;

Consecutive order count

Detailed discussion (step-by-step query):

Step-1 : Get the previous order_type for each order

SELECT *, LAG(Order_type) OVER(ORDER BY Seq) AS prev_Order_type 
FROM Order_batch;

Step-2: Group consecutive rows with same Order_type

(this is the trickiest part of the SQL query, forming groups of consecutive rows with the same order type)

SELECT *, 
SUM(CASE WHEN Order_type = prev_Order_type THEN 0 ELSE 1 END) OVER(ORDER BY Seq) AS grp
FROM 
(SELECT *, LAG(Order_type) OVER(ORDER BY Seq) AS prev_Order_type
 FROM Order_batch) s;

Consecutive rows in same order_type are grouped together

Step -3 :Use the above query output (step-2) as a common table expression or temporary table to group the data “grp” wise and find total count of orders in each group

WITH cte AS (
 SELECT Seq, Order_type, SUM(CASE WHEN Order_type = prev_Order_type THEN 0 ELSE 1 END) OVER(ORDER BY Seq) AS grp
 FROM (SELECT *, LAG(Order_type) OVER(ORDER BY Seq) AS prev_Order_type FROM Order_batch) s
)
SELECT  Order_type,grp , count(*) AS Order_count
FROM cte
GROUP BY Order_type, grp;

Step 4: In the final query, give each row an unique number

 
WITH cte AS (
 SELECT Seq, Order_type, SUM(CASE WHEN Order_type = prev_Order_type THEN 0 ELSE 1 END) OVER(ORDER BY Seq) AS grp
 FROM (SELECT *, LAG(Order_type) OVER(ORDER BY Seq) AS prev_Order_type FROM Order_batch) s
)
SELECT ROW_NUMBER() OVER() AS Seq, Order_type AS 'Order' ,count(*) AS 'Count'
FROM cte
GROUP BY Order_type, grp;

RDBMS — MySQL

Concepts used — Subquery , common table expression , aggregation window function , LAG() , ROW_NUMBER() ,CASE-WHEN statement , GROUP BY etc

Thank you !

Please drop your solution in the comment.

Please let me know in case of any issues in my solution or if you have any queries