SQL INTERVIEW QUESTION | Categorize Salespersons by Sales Amount in SQL

[@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.

[@HARSHRAJ-gp6ve]

with cte as( select salesperson,MAX(amount) as max_amount,MIN(amount) as min_amount,AVG(amount) as avg_amount FROM sales GROUP BY salesperson ),cte1 as( select sales.*,max_amount,min_amount,avg_amount FROM sales JOIN cte ON sales.salesperson=cte.salesperson ) select salesperson,amount, case when amount=min_amount THEN 'Low salary' when amount BETWEEN min_amount+1 and avg_amount-1 THEN 'below average' when amount=avg_amount THEN 'avearge salary' when amount BETWEEN avg_amount+1 and max_amount-1 THEN 'above average' when amount=max_amount THEN 'High salary' END AS salary_status FROM cte1;