SQL INTERVIEW QUESTION | Categorize Customers Based on Conditions

[@mayamajhi7842]

Explained well

[@datasculptor2895]

Thank you

[@user-gq6cg3ls7f]

Thank you for the good question My approach: with cte as( SELECT *, MONTH(DateOfPurchase) months, ROW_NUMBER() over (partition by CustomerID order by DateOfPurchase) RN, DATEADD(MONTH, -1 * ROW_NUMBER() over (partition by CustomerID order by DateOfPurchase), DateOfPurchase) bucket, count(DateOfPurchase) over (partition by CustomerID) cnt, SUM(revenue) over (partition by CustomerID) total_revenue, SUM(case when ModeOfPurchase = 'Online' then 1 else 0 end) over (partition by CustomerID) online_customer FROM CustomerPurchases ) select distinct customerID, case when months - RN =2 and cnt >= 6 then 'Regular Customer' when total_revenue>=5000 then 'Top Customer' when cnt = online_customer then 'Online Customer' Else 'Average Customer' END Type from cte

[@datasculptor2895]

Nice

[@akhilsingh5251]

Hi sir, Kindly put create and insert statements in comment section so , that we can save our time and do more questions. Thanks

[@datasculptor2895]

It’s there in description

[@ajittiwari4504]

with cte as ( SELECT customerid, sum(Revenue) over(partition by customerid) as total_spent, subdate(DateOfPurchase, interval row_number() over(partition by customerid order by DateOfPurchase) month) as cnt, case when ModeOfPurchase = 'Online' then 1 else 0 end as flag FROM CustomerPurchases) select customerid, case when count(cnt)>=5 then 'Regular customer' when total_spent>=5000 then 'Top customer' when min(flag) =1 then 'Online customer' else 'Average Customer' end as Category from cte group by customerid, total_spent;