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
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
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
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 ) ;
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);
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
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
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
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
)
;
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);
Nice 👍