Розв’язання реальних проблем за допомогою SQL

У моєму попередньому блозі я обговорював деякі складні запити, які часто задають на співбесідах, а також ті, які аналітики даних часто пишуть у своїй повсякденній роботі, розв'язуючи реальні проблеми.
У цьому блозі ми розглянемо рішення подібних типів проблем, використовуючи приклад даних про продажі.

Запит 1: Загальний дохід за регіонами

Мета: Обчислити загальний дохід для кожного регіону.

SELECT Region, SUM(Revenue) AS TotalRevenue  
FROM Sales GROUP BY Region ORDER BY TotalRevenue DESC;

Запит 2: Кумулятивний дохід за місяцями

Мета: Обчислити кумулятивний дохід за всіма місяцями для кожного регіону.

SELECT Region, Month, Revenue,   
 SUM(Revenue) OVER (PARTITION BY Region ORDER BY Month) AS CumulativeRevenue  
FROM Sales  
ORDER BY Region, Month;

Запит 3: Рейтинг представників з продажу за загальним прибутком

Мета: Присвоїти ранги представникам з продажу на основі їхнього загального прибутку.

SELECT SalesRep, SUM(Profit) AS TotalProfit,  
 RANK() OVER (ORDER BY SUM(Profit) DESC) AS ProfitRank  
FROM Sales  
GROUP BY SalesRep  
ORDER BY ProfitRank;

Запит 4: Порівняння місячного зростання прибутку (Lead і Lag)

Мета: Порівняти прибуток кожного місяця з наступним і попереднім місяцями.

SELECT Region, Month, Profit,  
 LEAD(Profit) OVER (PARTITION BY Region ORDER BY Month) AS NextMonthProfit,  
 LAG(Profit) OVER (PARTITION BY Region ORDER BY Month) AS PreviousMonthProfit,  
 Profit - LAG(Profit) OVER (PARTITION BY Region ORDER BY Month) AS ProfitChange  
FROM Sales  
ORDER BY Region, Month;

Запит 5: Категорія з найвищим доходом у кожному регіоні

Мета: Знайти топову категорію продуктів для кожного регіону за доходом.

SELECT Region, Category, SUM(Revenue) AS TotalRevenue,  
 RANK() OVER (PARTITION BY Region ORDER BY SUM(Revenue) DESC) AS CategoryRank  
FROM Sales  
GROUP BY Region, Category  
HAVING CategoryRank = 1;

Запит 6: Середня кількість проданих одиниць на місяць

Мета: Обчислити середню кількість одиниць, проданих на місяць для кожного регіону.

SELECT Region, AVG(UnitsSold) AS AvgUnitsSold  
FROM Sales  
GROUP BY Region;

Запит 7: Відсотковий внесок кожного продавця у дохід регіону

Мета: Визначити відсотковий внесок кожного продавця у загальний дохід регіону.

SELECT Region, SalesRep, SUM(Revenue) AS SalesRepRevenue,
SUM(Revenue) * 100.0 / SUM(SUM(Revenue)) OVER (PARTITION BY Region) AS RevenuePercentage  
FROM Sales  
GROUP BY Region, SalesRep;

Запит 8: Ковзаюче середнє прибутку за 2 місяці

Мета: Обчислити ковзаюче середнє прибутку за останні два місяці.

SELECT Region, Month, Profit,  
 AVG(Profit) OVER (PARTITION BY Region ORDER BY Month ROWS BETWEEN 1   
PRECEDING AND CURRENT ROW) AS RollingProfit  
FROM Sales  
ORDER BY Region, Month;

Запит 9: Визначити регіони, що виконують цільові показники прибутку

Мета: Знайти регіони, де прибуток перевищує 15 000 доларів хоча б в один місяць.

SELECT DISTINCT Region FROM Sales WHERE Profit > 15000;

Запит 10: Доходи за перший та останній місяць для кожного регіону

Мета: Отримати доходи за перший та останній місяць для кожного регіону.

SELECT Region,  
 FIRST_VALUE(Revenue) OVER (PARTITION BY Region ORDER BY Month) AS FirstMonthRevenue,  
 LAST_VALUE(Revenue) OVER (PARTITION BY Region ORDER BY Month ROWS   
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastMonthRevenue  
FROM Sales  
GROUP BY Region;

Перекладено з: Solving Real World SQL Problems