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

Task:

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

Input:

Desired Output:

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

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

__Task:__

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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

Task:

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

Input:

Desired Output:

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

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.