SQL INTERVIEW QUESTION | Categorize Salespersons by Sales Amount in SQL

DML Script: datasculptor.b...
Feedback: forms.gle/NQuC...
Playlists:
SQL Interview Questions: • SQL Interview questions
Recursive CTE: • Recursive CTE
Power BI: • Power BI and DAX
Data Modeling: • Data Modelling
Generative AIs: • Generative AIs
Excel: • Excel
Follow me
Linkedin: / data-sculptor-93a00b2a8
Instagram: / datasculptor2895

[@vijaygupta7059]

Using window function in MSSQL DB: with cte as ( Select * ,avg(amount)over(partition by salesperson) as avg_amount ,min(amount)over(partition by salesperson) as min_amount ,max(amount)over(partition by salesperson) as max_amount from sales ) Select salesperson, amount , case when amount=min_amount then 'Lowest' when amount=max_amount then 'Highest' when amountavg_amount then 'Above Average' end as Catogery from cte

[@datasculptor2895]

Nice 👍

[@sabesanj5509]

Awesome brother.

[@kujiama7946]

im new learing SQL im gonna go back to this question right here soon!

[@datasculptor2895]

Good luck

[@kujiama7946]

@@datasculptor2895 Hello Brother im back # usage of windows function so no need to think about anything max avg and lowest SELECT salesperson, amount, CASE WHEN amount = high THEN "Highest" WHEN amount > avg AND amount < high THEN "Above Average" WHEN amount = avg THEN "Average" WHEN amount < avg AND amount > low THEN "Below Average" WHEN amount = low THEN "Lowest" END as Category FROM ( SELECT *, avg(amount) over(partition by salesperson) as avg, -- average max(amount) over(partition by salesperson) as high, -- highest min(amount) over(partition by salesperson) as low -- highest FROM sales ) AS Sales

[@monasanthosh9208]

Select *,Case When Amount=Min(Amount) over (Partition by Sales_person Order by Amount asc) Then "Lowest" When Amount=Max(Amount) over (Partition by Sales_person Order by Amount Desc) Then "Highest" When Amount=Avg(Amount) over (Partition by Sales_person Range between unbounded preceding and unbounded following) Then "Average" When Amount>Avg(Amount) over (Partition by Sales_person Range between unbounded preceding and unbounded following) Then "Above Average" When Amount

[@datasculptor2895]

Nice. Please subscribe to my channel

[@sabesanj5509]

Brother please mention the question in description section or in blog site.

[@datasculptor2895]

Ok.