SQL Data Analysis Interview Question #35/100 | SQL Challenge | SQL Tutorials | Consecutive days sale

[@MusicalShorts-hn1px]

with cte as
(
select *, day(saledate)-rank() over(partition by productid order by saledate) as rnk
from sales
)
select productid,min(SaleDate) as saledate
from cte
group by rnk, ProductID
having count(productid)>1

[@user-gq6cg3ls7f]

with cte as(
select *,
DATEADD(day, -1 * ROW_NUMBER() over (order by SaleID), SaleDate) bucket
from Sales_Sun
), cte2 as(
select *, count(bucket) over (partition by productId, bucket order by ProductId ) cnt from cte
)
select distinct ProductID,
FIRST_VALUE(SaleDate) over (partition by bucket order by SaleID) first_sale_date
from cte2 where cnt=2

[@Chathur732]

select productid, saledate
from (
select *, lead(saledate) over(partition by ProductID order by saleid),
abs(saledate - lead(saledate) over(partition by ProductID order by saleid)) as difference
from sales)
where difference = 1

[@chuphal_himanshu]

select a.*
from saless a
join saless b on b.saledate = date_add( a.saledate, interval '1 day')
where abs(a.saledate - b.saledate) = 1
and a.productid = b.productid

[@ChaitanyaKariya-x4q]

select saleid,productid , saledate , quantity from(
select *,
count(consecutive_date) over(partition by consecutive_date)
from (
select * ,
(saledate - interval '1 day' * rank)::date as consecutive_date
from
(select * ,
dense_rank() over(partition by productid order by saledate) as rank
from Sales_3) x) y) z
where count >= 2 and rank =1

[@harshitsalecha221]

WITH Cte1 AS (SELECT *,
DATEDIFF(LEAD(saledate) OVER(PARTITION BY productid ORDER BY saledate),saledate) as diff1,
DATEDIFF(saledate,LAG(saledate) OVER(PARTITION BY productid ORDER BY saledate)) as diff2,
ROW_NUMBER() OVER(PARTITION BY productid ORDER BY saledate) as rn
FROM sales)
SELECT saleid,productid,saledate,Quantity
FROM cte1
WHERE diff1=1 OR diff2=1 AND rn=1;

[@yashmathur5609]

Instead of joins I used lag.
My approach
With cte as (
SELECT SALEID, PRODUCTID, QUANTITY, SALEDATE, LAG (SALEDATE) OVER(PARTITION BY PRODUCTID ORDER BY SALEDATE) AS PREV_ DATE FROM SALES) SELECT PRODUCTID, min (PREV DATE) as dates FROM CTE WHERE abs (DATEDIFF(DAY,SALEDATE, PREV_DATE)) =1
group by PRODUCTID
Can you please reviews final answer and say if it's correct or wrong approach

[@pranavbagal9988]

Perfect

[@Soul-f3v]

with cte as(
select saleID,ProductID as Productid,lead(SaleDate) over(order by SaleID) as nextDay from Sales
)
select s.ProductID,min(s.SaleDate) from Sales s join cte c on c.SaleID=s.SaleID where DateAdd(day,1,s.SaleDate)=c.nextDay and s.ProductID = c.Productid group by s.ProductID;

[@HARSHRAJ-gp6ve]

with cte as(
select Sales.*,LEAD(SaleDate)OVER(PARTITION BY ProductID) as x1 FROM Sales
),cte1 as(
select ProductID,DATEDIFF(x1,SaleDate) as x2 FROM cte
),cte2 as(
select ProductID,COUNT(x2) FROM cte1 where x2=1 GROUP BY ProductID HAVING COUNT(x2)>=1
),cte3 as(
select Sales.*,DENSE_RANK()OVER(PARTITION BY productID ORDER BY SaleDate) as x3 FROM Sales
)
select SaleID,cte3.ProductID,saleDate,quantity FROM cte2 JOIN cte3 ON cte2.ProductID=cte3.
ProductID where x3=1;

[@md.ashaduszzaman]

with test as (select t.*, lead(saledate, 1) over (partition by productid order by productid) saledate1 from test_sales t)
select saleid, productid, saledate, quantity from test where saledate1-saledate=1