Write a SQL query to find the customers who have placed orders on consecutive days.
Orders table creation script:
# Orders table creation script
CREATE TABLE orders (
OrderID INT,
OrderDate DATE,
CustomerID INT
);
Data insertion script
INSERT INTO orders (OrderID, OrderDate, CustomerID)
VALUES
(1, '2023-06-20', 1),
(2, '2023-06-21', 2),
(3, '2023-06-22', 3),
(4, '2023-06-21', 1),
(5, '2023-06-23', 3),
(6, '2023-06-22', 1),
(7, '2023-06-26', 4),
(8, '2023-06-27', 4),
(9, '2023-06-29', 4),
(10, '2023-06-29', 5),
(11, '2023-06-30', 5);
You can check from the below image that, customer 1 (orange circle) , customer 3 (green circle) , customer 5 (blue circle) have made purchases on consecutive days
The query in MySQL can be written as follows:
# Find the customers who have placed orders on consecutive days.
WITH cte AS
(SELECT CustomerId,
CASE
WHEN LEAD(OrderDate) Over(PARTITION BY customerid) IS NULL THEN 1
ELSE ABS(OrderDate -LEAD(OrderDate) Over(PARTITION BY customerid) )
END AS 'Day_gap'
FROM orders
)
SELECT CustomerId
FROM cte
GROUP BY CustomerId
HAVING count(*) = SUM(Day_gap)
AND count(*) >1
;
Concepts used - Common Table expression / WITH subquery , windowing function, CASE-WHEN statement ,GROUP BY, HAVING etc
Thanks!
Data Engineer | Apache Spark | Python | Databricks | Palantir
8mowith cte1 as ( select *, LEAD(orderdate, 1) OVER (PARTITION BY customerId ORDER BY orderdate) as next_date from orders ), cte2 as ( select *, next_date-orderdate as abs_date from cte1 ) select customerId from cte2 where abs_date is not null group by customerid having count(customerId) = sum(abs_date)
DATA ENGINEER | ETL | AWS | PYSPARK| SAIL ANALYTICS | EX - MU SIGMA |
11moFor me it's showing you can't use lead without order by clause 'The function 'LEAD' must have an OVER clause with ORDER BY'
Power BI Developer || DAX ||SQL ||Snowflake
1ywith cte as ( select *, lag(orderdate) over(partition by customerid order by orderdate) as previous_date from orders ) select distinct c1.CustomerID from cte c1 inner join cte c2 on c1.CustomerID=c2.CustomerID and DATEDIFF(day,c2.previous_date,c1.OrderDate)=1
Solution Architect - Microsoft 365 | Power Platform | Dynamics 365 | Azure | SharePoint
1yI got same result using below query, please let me know if this is not good practice. SELECT CUSTOMERS.* FROM Customers inner Join Orders O1 On Customers.CustomerID = O1.CustomerID Inner Join Orders O2 On Customers.CustomerID = O2.CustomerID WHERE DATEDIFF(DAY, O1.OrderDate, O2.OrderDate) =1