SQL INTERVIEW QUESTION | Categorize Salespersons by Sales Amount in SQL

Подписаться 1,7 тыс.

Просмотров 1,5 тыс.

0% 0

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

Опубликовано:

4 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 10

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