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.