How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

[@shashank_1180]

with cte as(
SELECT *
,LAG(order_date,1)over(partition by customer_name order by order_date) prev
,LEAD(order_date,1)over(partition by customer_name order by order_date) next
FROM Orders
)
select distinct customer_name
from cte
where DATEDIFF(DAY,prev,order_date)=1 and DATEDIFF(DAY,order_date,next)=1

[@sudhindrab1606]

with cte as (SELECT *,lead(order_date)over(partition by customer_name order by order_date) as d1
FROM Orders),
cte2 as(
select *,DATEDIFF(d,order_date,d1) as date_diff from cte ),
cte3 as(
select *,count(date_diff)over(partition by customer_name) as repetative_counts from cte2
where date_diff=1 )
select distinct customer_name from cte3 where repetative_counts>=3

[@DE_Pranav]

with cte as(
select * ,
day(order_date)- ROW_NUMBER() over (partition by customer_name order by order_date) as diff
from Orders
)
select customer_name
from cte
group by customer_name,diff
having count(1)>3

[@bhavanig2582]

Simplified Answer with MATCH_RECOGNIZE
SELECT * FROM Orders
match_recognize(
partition by customer_name
order by order_date
pattern (p{2,})
define
p as case when datediff('DAY',lag(order_date), order_date) =1 or datediff('DAY',order_date, lead(order_date)) =1 then 1 end
)
;

[@sabesanj5509]

WITH ConsecutiveOrders AS (
SELECT
customer_name,
order_date,
LEAD(order_date, 1) OVER (PARTITION BY customer_name ORDER BY order_date) AS next_order_date,
LEAD(order_date, 2) OVER (PARTITION BY customer_name ORDER BY order_date) AS third_order_date
FROM
Orders
)
SELECT DISTINCT customer_name
FROM ConsecutiveOrders
WHERE next_order_date = DATE_ADD(order_date, INTERVAL 1 DAY)
AND third_order_date = DATE_ADD(order_date, INTERVAL 2 DAY);

[@datasculptor2895]

Nice 👍