# SQL Window Function Exercises and Solutions – Set 2

SQL window functions exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are designed like fun puzzles, take your time and try to solve. By the end of this set, you should feel confident in solving the hardest SQL Window function problems.

If you haven’t read the tutorial yet, read SQL Window Functions – Must Read Guide.

Try solving hands-on in MySQL using the link provided in each question.

Author’s note: The solutions provided is (mostly) one of the several possible solutions. The goal is not to conform with the provided solution. Try reach the desired output shown.

## Q1. Calculate the running total of sales.

Difficulty Level: Intermediate

From the sales table, calculate the running total of amount.

Input:

sale_id sale_date amount
1 2022-01-01 100
2 2022-01-05 150
3 2022-02-15 200
4 2022-02-20 250
5 2022-03-10 300

Desired Output:

sale_date amount running_total
2022-01-01 100 100
2022-01-05 150 250
2022-02-15 200 450
2022-02-20 250 700
2022-03-10 300 1000

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT sale_date,
amount,
sum(amount) over (order by sale_date) as running_total
FROM sales;


## Q2. Get the difference between the current sale and the previous sale.

Difficulty Level: Intermediate

From the sales table, calculate the difference between the current sale and the previous sale.

__Input:__

| sale_id | sale_date | amount |
| ——- | ———- | —— |
| 1 | 2022-01-01 | 100 |
| 2 | 2022-01-05 | 150 |
| 3 | 2022-02-15 | 200 |
| 4 | 2022-02-20 | 250 |
| 5 | 2022-03-10 | 300 |

__Desired Output:__

| sale_date | amount | difference |
| ———- | —— | ———- |
| 2022-01-01 | 100 | 100 |
| 2022-01-05 | 150 | 50 |
| 2022-02-15 | 200 | 50 |
| 2022-02-20 | 250 | 50 |
| 2022-03-10 | 300 | 50 |

Solve Hands-On: [HERE](https://www.db-fiddle.com/f/xdbTd8VZi6GYaFFzNkDUHz/1), Table Schema and data: [Gist](https://gist.github.com/machinelearningplus/752971b25fcc4f15d53765c53d3ff3a1)

Show Solution
SELECT sale_date,
amount,
amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS difference
FROM Sales;


## Q3. Find the highest salary in each department

Difficulty Level: Intermediate

From the employees table, calculate the maximum salary in each department.

Input:

dept_id emp_id salary
10 1 50000
10 2 55000
20 3 60000
20 4 65000

Desired Output:

dept_id emp_id salary max_salary
10 1 50000 55000
10 2 55000 55000
20 3 60000 65000
20 4 65000 65000

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT dept_id,
emp_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS max_salary
FROM Employees;


## Q4. Find the difference between the salary of an employee and the average salary of their department

Difficulty Level: Intermediate

From the employees table, find the difference between the salary of an employee and the average salary of their department.

Input:

dept_id emp_id salary
10 1 50000
10 2 55000
20 3 60000
20 4 65000

Desired Output:

emp_id dept_id salary avg_dept_salary diff_from_avg
1 10 50000 52500 -2500
2 10 55000 52500 2500
3 20 60000 62500 -2500
4 20 65000 62500 2500

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT emp_id,
dept_id,
salary,
AVG(salary) OVER (PARTITION BY dept_id) AS avg_dept_salary,
salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg
FROM Employees;


## Q5. Calculate the 2-day moving average for the stock prices.

Difficulty Level: Intermediate

From the stockprices table, find the 2-day moving average.

Input:

date price
2022-01-01 100.5
2022-01-02 101.75
2022-01-03 102
2022-01-04 103
2022-01-05 103.5
2022-01-06 107

Desired Output:

date price moving_avg
2022-01-01 100.5 100.5
2022-01-02 101.75 101.125
2022-01-03 102 101.875
2022-01-04 103 102.5
2022-01-05 103.5 103.25
2022-01-06 107 105.25

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT date, price,
AVG(price) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM StockPrices;


## Q6. Calculate the difference in days between joining dates

Difficulty Level: Intermediate

Calculate the difference in days between each employee’s joining date and the previous employee’s joining date.

Input:

emp_id join_date salary
1 2022-01-01 1000
2 2022-01-10 1100
3 2022-01-15 1200

Desired Output:

emp_id join_date day_diff
1 2022-01-01 NULL
2 2022-01-10 9
3 2022-01-15 5

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
with sub as (SELECT emp_id,
join_date,
lag(join_date, 1) over (ORDER BY join_date) as prev_date
FROM employees)
select *,
DATEDIFF(Date(join_date), date(prev_date)) AS day_diff
from sub;


Questions numbers Q7 onwards uses the same table as below. To avoid repetition, the input is printed only for Q7, please use the same for the full question set.

## Q7. Find the cumulative sales amount for each product.

Difficulty Level: Intermediate

From Sales table, find the cumulative sales amount for each product.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate CumulativeSales
1 2023-01-01 100
1 2023-01-02 250
1 2023-01-03 370
2 2023-01-01 50
2 2023-01-02 120
2 2023-01-03 180
3 2023-01-01 20
3 2023-01-03 50

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
sum(SalesAmount) over(partition by ProductID order by SaleDate) AS CumulativeSales
FROM sales;


## Q8. Compute the average sales for each product over all days.

Difficulty Level: Intermediate

From Sales table, find the cumulative sales amount for each product.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate CumulativeSales
1 2023-01-01 100
1 2023-01-02 250
1 2023-01-03 370
2 2023-01-01 50
2 2023-01-02 120
2 2023-01-03 180
3 2023-01-01 20
3 2023-01-03 50

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
sum(SalesAmount) over(partition by ProductID order by SaleDate) AS CumulativeSales
FROM sales;
oyees)
select *,
DATEDIFF(Date(join_date), date(prev_date)) AS day_diff
from sub;


## Q9. Compute the average sales for each product over all days

Difficulty Level: Intermediate

From Sales table, compute the average sales for each product over all days.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID AverageSales
1 123.33
1 123.33
1 123.33
2 60
2 60
2 60
3 25
3 25

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
AVG(SalesAmount) OVER(PARTITION BY ProductID) AS AverageSales
FROM Sales;


## Q10. Calculate the difference between the current day’s sales and the previous day’s sales

Difficulty Level: Intermediate

From Sales table, Calculate the difference between the current day’s sales and the previous day’s sales for each product.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate DifferenceFromPrevious
1 2023-01-01
1 2023-01-02 50
1 2023-01-03 -30
2 2023-01-01
2 2023-01-02 20
2 2023-01-03 -10
3 2023-01-01
3 2023-01-03 10

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
SalesAmount - LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS DifferenceFromPrevious
FROM Sales;


## Q11. Get the next sale date for each product sale.

Difficulty Level: Intermediate

From Sales table, Get the next sale date for each product sale.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate NextSaleDate
1 2023-01-01 2023-01-02
1 2023-01-02 2023-01-03
1 2023-01-03
2 2023-01-01 2023-01-02
2 2023-01-02 2023-01-03
2 2023-01-03
3 2023-01-01 2023-01-03
3 2023-01-03

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
LEAD(SaleDate) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS NextSaleDate
FROM Sales;


## Q12. Find the total sales of the previous day for each product

Difficulty Level: Intermediate

From Sales table, Find the total sales of the previous day for each product.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate PreviousDaySales
1 2023-01-01
1 2023-01-02 100
1 2023-01-03 150
2 2023-01-01
2 2023-01-02 50
2 2023-01-03 70
3 2023-01-01
3 2023-01-03 20

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS PreviousDaySales
FROM Sales;


## Q13. Calculate the average sales amount of the previous two days

Difficulty Level: Hard Task: From Sales table, for each sale, calculate the average sales amount of the previous two days (including the current day) for each product. Input:
SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00
Desired Output:
ProductID SaleDate AvgOfLastTwoDays
1 2023-01-01 100
1 2023-01-02 125
1 2023-01-03 135
2 2023-01-01 50
2 2023-01-02 60
2 2023-01-03 65
3 2023-01-01 20
3 2023-01-03 25
Solve Hands-On: HERE, Table Schema and data: Gist Show Solution
SELECT
ProductID,
SaleDate,
AVG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS AvgOfLastTwoDays
FROM Sales;


## Q14. Find the date of maximum sale

Difficulty Level: Hard

From Sales table, for each sale, get the sale date where maximum sales were made in the previous 2 days for each product.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate MaxSaleDateLastTwoDays
1 2023-01-02 2023-01-02
1 2023-01-03 2023-01-02
1 2023-01-01 2023-01-02
2 2023-01-02 2023-01-02
2 2023-01-03 2023-01-02
2 2023-01-01 2023-01-02
3 2023-01-03 2023-01-03
3 2023-01-01 2023-01-03

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
FIRST_VALUE(SaleDate) OVER(PARTITION BY ProductID ORDER BY SalesAmount DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MaxSaleDateLastTwoDays
FROM Sales;


## Q15. Calculate the percentage contribution of each product's sale

Difficulty Level: Hard

From Sales table, Calculate the percentage contribution of each product's sale to the total sales of that day.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate MaxSaleDateLastTwoDays
1 2023-01-02 2023-01-02
1 2023-01-03 2023-01-02
1 2023-01-01 2023-01-02
2 2023-01-02 2023-01-02
2 2023-01-03 2023-01-02
2 2023-01-01 2023-01-02
3 2023-01-03 2023-01-03
3 2023-01-01 2023-01-03

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
SalesAmount,
round((SalesAmount / SUM(SalesAmount) OVER(PARTITION BY SaleDate)) * 100, 2) AS PercentageContribution
FROM sales;


## Q16. Calculate the third highest sales amount

Difficulty Level: Hard

For each product, get the third highest sales amount and its corresponding sale date.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate SalesAmount
1 2023-01-01 100.00
2 2023-01-01 50.00

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
SalesAmount
FROM (
SELECT
ProductID,
SaleDate,
SalesAmount,
DENSE_RANK() OVER(PARTITION BY ProductID ORDER BY SalesAmount DESC) AS rnk
FROM sales
) AS T
WHERE rnk = 3;


## Q17. Calculate the moving variance of the last 3 sales amounts for each product.

Difficulty Level: Hard

From sales table, calculate the moving variance of the last 3 sales amounts for each product.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 12 120.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate MovingVariance
1 2023-01-01 0
1 2023-01-02 625
1 2023-01-03 422.22222222222223
2 2023-01-01 0
2 2023-01-02 100
2 2023-01-03 66.66666666666667
3 2023-01-01 0
3 2023-01-03 25

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
VARIANCE(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingVariance
FROM sales;


## Q18. Find the product that had the least sales amount difference compared to the previous day.

Difficulty Level: Hard

From sales table, For each sale date, find the product that had the least sales amount difference compared to the previous day.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

SaleDate ProductID Diff
2023-01-01 1
2023-01-02 2 20.00
2023-01-03 2 10.00

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
WITH Differences AS (
SELECT
ProductID,
SaleDate,
ABS(SalesAmount - LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate)) AS Diff
FROM sales
)

SELECT
SaleDate,
ProductID,
Diff
FROM (
SELECT
SaleDate,
ProductID,
Diff,
ROW_NUMBER() OVER(PARTITION BY SaleDate ORDER BY Diff ASC) AS rnk
FROM Differences
) AS T
WHERE rnk = 1;


## Q19. Determine the average change in sales amounts

Difficulty Level: Hard

From sales table, for each product, determine the average change in sales amounts corresponding to the previous sale day.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

PRODUCTID AVGCHANGE
1 30.000000
2 5.000000
3 10.000000

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
with tbl as (SELECT
PRODUCTID,
SALEDATE,
(SalesAmount - LAG(SalesAmount, 1) OVER(PARTITION BY PRODUCTID ORDER BY SALEDATE)) AS diff,
COUNT(*) over (PARTITION BY PRODUCTID) - 1 as num_changes
FROM sales),

tbl2 as (
SELECT PRODUCTID,
max(SALEDATE) as SALEDATE,
sum(diff) as SUMDIFF,
max(num_changes) as TOTALCHANGES
from tbl
group by ProductID)
SELECT PRODUCTID,
SUMDIFF/TOTALCHANGES as AVGCHANGE
FROM tbl2;


## Q20. Find the median sales amount for each product.

Difficulty Level: Hard

From sales table, for each product, determine the average change in sales amounts corresponding to the previous sale day.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID Median
1 150.000000
2 60.000000
3 25.000000

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
WITH RankedSales AS (
SELECT
ProductID,
SalesAmount,
ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SalesAmount) AS rn,
COUNT(*) OVER(PARTITION BY ProductID) AS cnt
FROM sales
)

SELECT
ProductID,
AVG(SalesAmount) AS Median
FROM RankedSales
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
GROUP BY ProductID;


## Q20. Find the difference from the average for each entry

Difficulty Level: Hard

For each sale, calculate the difference from the monthly average of the product's sales amount.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate SalesAmount DiffFromMonthlyAvg
1 2023-01-01 100.00 -36.666667
1 2023-01-02 150.00 13.333333
1 2023-01-03 160.00 23.333333
2 2023-01-01 50.00 -10.000000
2 2023-01-02 70.00 10.000000
2 2023-01-03 60.00 0.000000
3 2023-01-01 20.00 -5.000000
3 2023-01-03 30.00 5.000000

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
SalesAmount,
SalesAmount - AVG(SalesAmount) OVER(PARTITION BY ProductID, EXTRACT(MONTH FROM SaleDate)) AS DiffFromMonthlyAvg
FROM sales;


## Q21. Rank products by the variability

Difficulty Level: Hard

For each sale, calculate the difference from the monthly average of the product's sales amount.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID STDV VariabilityRank
1 32.1455 1
2 10 2
3 7.0711 3

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
ROUND(STDDEV_SAMP(SalesAmount),4) as STDV,
RANK() OVER(ORDER BY STDDEV_SAMP(SalesAmount) DESC) AS VariabilityRank
FROM sales
GROUP BY ProductID;


## Q22. Calculate a 3-day centered moving average

Difficulty Level: Hard

Calculate a 3-day centered moving average for each product's sales amount.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate SalesAmount CenteredMovingAverage
1 2023-01-01 100.00 125.000000
1 2023-01-02 150.00 136.666667
1 2023-01-03 160.00 155.000000
2 2023-01-01 50.00 60.000000
2 2023-01-02 70.00 60.000000
2 2023-01-03 60.00 65.000000
3 2023-01-01 20.00 25.000000
3 2023-01-03 30.00 25.000000

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
SELECT
ProductID,
SaleDate,
SalesAmount,
AVG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS CenteredMovingAverage
FROM sales;


## Q23. Determine if the sales amount of each product was above or below average

Difficulty Level: Hard

For each sale date, determine if the sales amount of each product was above or below its previous 3 days average.

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate SalesAmount ComparisonToLast3Days
1 2023-01-01 100.00 Below
2 2023-01-01 50.00 Below
3 2023-01-01 20.00 Below
1 2023-01-02 150.00 Above
2 2023-01-02 70.00 Above
1 2023-01-03 160.00 Above
2 2023-01-03 60.00 Below
3 2023-01-03 30.00 Above

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
WITH threeDayAverage AS (
SELECT
ProductID,
SaleDate,
AVG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS avg_last_3_days
FROM sales
)

SELECT
S.ProductID,
S.SaleDate,
S.SalesAmount,
CASE
WHEN S.SalesAmount > A.avg_last_3_days THEN 'Above'
ELSE 'Below'
END AS ComparisonToLast3Days
FROM sales S
JOIN threeDayAverage A ON S.ProductID = A.ProductID AND S.SaleDate = A.SaleDate;


## Q24. Calculate the cumulative sales growth rate

Difficulty Level: Hard

Calculate the cumulative sales growth rate for each product. (Sales growth rate from one day to the next is (TodaysSale−YesterdaysSale)/(YesterdaysSale).

Input:

SaleID ProductID SaleDate QuantitySold SalesAmount
1 1 2023-01-01 10 100.00
2 2 2023-01-01 5 50.00
3 3 2023-01-01 15 20.00
4 1 2023-01-02 15 150.00
5 2 2023-01-02 7 70.00
6 1 2023-01-03 16 160.00
7 2 2023-01-03 6 60.00
8 3 2023-01-03 16 30.00

Desired Output:

ProductID SaleDate SalesAmount cumulative_growth_rate
1 2023-01-01 100.00
1 2023-01-02 150.00 0.500000
1 2023-01-03 160.00 0.566667
2 2023-01-01 50.00
2 2023-01-02 70.00 0.400000
2 2023-01-03 60.00 0.257143
3 2023-01-01 20.00
3 2023-01-03 30.00 0.500000

Solve Hands-On: HERE, Table Schema and data: Gist

Show Solution
WITH GrowthRates AS (
SELECT
ProductID,
SaleDate,
SalesAmount,
(SalesAmount - LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate)) / LAG(SalesAmount) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS growth_rate
FROM sales
)

SELECT
ProductID,
SaleDate,
SalesAmount,
SUM(growth_rate) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS cumulative_growth_rate
FROM GrowthRates;
`

## Next Steps

1. Try the SQL Window Functions Set 1 if you haven't already done.
2. To become real solid at SQL, the SQL courses (basic, intermediate and advanced) are included as part of the Machine Learning Plus - Complete Data Science University Access. Subscribe.

Course Preview