SQL INTERVIEW QUESTION | Categorize Salespersons by Sales Amount in SQL
Vložit
- čas přidán 4. 09. 2024
- 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
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
Nice 👍
Awesome brother.
im new learing SQL im gonna go back to this question right here soon!
Good luck
@@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
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
Nice. Please subscribe to my channel
Brother please mention the question in description section or in blog site.
Ok.