SQL Window Functions Exercises – Practice to perfection

SQL window functions is considered a ‘hard’ concept in SQL. This set of exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are of three levels of difficulty: Easy, Intermediate and Hard.

If you haven’t read the tutorial yet, read SQL Window Functions – Made Simple and Intuitive.

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. Find the running cumulative total demand.

Difficulty Level: Intermediate

Task:

From the demand2 table, find the cumulative total sum for qty.

Input:

SELECT * FROM demand2;

Desired Output:

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

SELECT day, 
       qty,
       SUM(qty) OVER(ORDER BY day) as cumQty 
FROM demand2;

Q2. Find the running cumulative total demand by product.

Difficulty Level: Intermediate

Task:

From the demand table, find the cumulative total sum for qty for each product category.

Input:

Desired Solution:

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

SELECT product,
	day,
        qty,
        SUM(qty) OVER(PARTITION BY product ORDER BY day) as CUMSUM
FROM demand;

Q3. When are the top 2 worst performing days for each product?

Difficulty Level: Intermediate

Task:

Extract the two worst performing days of each product in terms of number of qty sold. Paraphrasing it: Get the days corresponding to the two minimum most values of qty for each product.

Input:

Desired Solution:

Solve Hands-On: HERE, Table Schema: Gist

with tbl as (SELECT PRODUCT,
                    qty,
                    day,
                    row_number() over (partition by product order by qty) as RN
FROM demand)
select product, day, qty
from tbl a
where RN in (1,2)

Q4. Find the percentage increase in qty compared to the previous day.

Difficulty Level: Intermediate

Task:

Sort the table by qty for each product and compute the percentage increase (or decrease) compared to the previous day.

Input:

Desired Solution:

Solve Hands-On: HERE, Table Schema: Gist

with tbl as (select product, 
                    day, 
                    qty, 
                    lag(qty, 1) over (partition by product order by day) as qty_lag
from demand)
select *,
round(((qty - qty_lag)/qty_lag) * 100, 2) as perc_increase
from tbl
where qty_lag is not null;

Q5. Show the minimum and maximum ‘qty’ sold for each product as separate columns.

Difficulty Level: Easy

Task:

Create two new columns in the table that shows the minimum and the maximum quantity sold for each product.

Input:

Desired Solution:

Solve Hands-On: HERE, Table Schema: Gist

select *,
        min(qty) over(partition by product) as min_qty, 
        max(qty) over(partition by product) as max_qty 
from
demand;

Q6. Calculate the difference between the second largest and the second smallest sales qty

Difficulty Level: Hard

Task:

Calculate the diffence between the second largest and the second smallest sales qty for each product.

Input:

Desired Output:

Solve Hands-On: HERE, Table Schema: Gist

select product, 
 day, 
 qty
from
  (select *,
  row_number() over (partition by product order by qty) as rownum, 
  count(*) over (partition by product) as total_recs
  from
  demand) a
where (rownum = 2) or rownum = (total_recs - 1);

Q7. On each day, which product had the highest sales?

Difficulty Level: Intermediate

Task:

Create a table to show the day and the names of the product the the highest qty sale.

Input:

Desired Output:

Solve Hands-On: HERE, Table Schema: Gist

with tbl as (
  select day, 
          product,
          qty,
          max(qty) over (partition by day) as maxqty
  from demand)
select day,
	product 
from tbl
where qty = maxqty;

Questions numbers Q8 to Q15 uses the same table as below.

Q8. Create row numbers in increasing order of sales.

Difficulty Level: Intermediate

Task:

Create row numbers in increasing order of sales, starting with 1 in each location

Input:

Desired Output:

Solve Hands-On: HERE, Table Schema: Gist

select *,
row_number() over(partition by location order by sales) as rn
from demand;

Q9. Find the top products (Rank 1 and 2) within each location

Difficulty: Intermediate

Task:

Extract the names of top selling products (rank 1 and 2) in each location. That is, the products with highest and second highest sales in each region.

Input:

Desired Output:

Solve Hands-On: HERE, Table Schema: Gist

drop table if exists temp;

create table temp as
with tbl as (select location, 
                    product,
                    sum(sales) as total_sales
from demand
group by location, product
order by location, total_sales desc)
select *, 
row_number() over (partition by location order by total_sales desc) as rn
from tbl;

select * from temp
where rn <= 2;

Q10. What is the total sales from Top 3 products in each location?

Difficulty: Intermediate

Task:

Calculate the total sales from the top 3 best selling products in each location.

Input:

Desired Output:

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

select location,
		product,
        total_sales,
        rn
from 
(select *, 
	row_number() over(partition by location order by total_sales desc) as rn
from (select location, 
              product, 
              sum(sales) as total_sales
      from demand
      group by location, product
      order by location, total_sales desc)
 ) 
 where rn <= 3;

Q11. Calculate the proportion of sales from each location

Difficulty: Intermediate

Task:

Calculate the proportion of sales from each location and show this against each record.

Input:

Desired Output:

SELECT *,
total_location,
round(cast(total_location as float) / cast(total_sales as float), 4) as ratio
from
(
  SELECT *,
  sum(sales) over(partition by location rows between unbounded preceding and unbounded following) as total_location, 
  sum(sales) over(rows between unbounded preceding and unbounded following) as total_sales 
  FROM demand
);

Q12. Which products contribute to top 80% of total sales?

Difficulty: Hard

Task:

Calculate and extract the best selling products whose total sales does not exceed 80% of overall sales. Show the cumulative percentage contribution to sales as a percentage as well.

Input:

Desired Output:

Solve Hands-On: HERE, Table Schema: Gist

SELECT *,
round((cum_total_sales/total_sales), 4) as cum_perc_sales
FROM
(select product,
	row_number() over (order by total_product_sales) as rn,
        total_product_sales,
        sum(total_product_sales) over (order by total_product_sales) as cum_total_sales,
 	sum(total_product_sales) over (rows between unbounded preceding and unbounded following) as total_sales
from
  (select *, 
  sum(sales) as total_product_sales
  from demand
  group by product
  order by total_product_sales desc
  )
)
where perc_sales <= 0.8;

Q13. What is the median value of sales overall?

Difficulty: Intermediate

Task:

Calculate the median sales value overall irrespective of product or location.

Input:

Desired Output:

Solve Hands-On: HERE, Table Schema: Gist

Note: SQLite does not support floor and ceil functions, so make sure to try out in MySQL or a db that supports these functions.
ion;
```

select location, 
       avg(sales) as median
from
  (select *,
  row_number() over (order by sales) as rn,
  count(*) over () as total_rows
  from demand) as tbl
where rn in (floor(total_rows/2), ceil(total_rows/2))
group by location;

Q14. What is the median value of sales for each location?

Difficulty: Hard

Task:

Calculate the median sales value across all product within each location.

Input:

Desired Output:

Note: SQLite does not support floor and ceil functions, so make sure to try out in MySQL or a db that supports these functions.

Solve Hands-On: HERE, Table Schema: Gist

select location, 
       avg(sales) as median_sales
from
 (select *
  from
        (select *,
        row_number() over (partition by location order by sales) as rn,
        count(*) over (partition by location) as total_rows
        from demand) as tbl
where rn in (floor(total_rows/2), ceil(total_rows/2))) as sub
group by location;

Q15. Which product has the largest refund rate overall?

Difficulty: Intermediate

Task:

Find out which product had the highest refund rate and how much.

Input:

Desired Output:

Solve Hands-On: HERE, Table Schema: Gist

with tbl as (select *,
      cast(total_refunds/total_sales as float) as refund_rate
  from
  (
    select product,
            sum(sales) as total_sales,
            sum(refunds) as total_refunds
    from demand
    group by product
  ) as sub
  order by refund_rate desc)
  select * from tbl
  where refund_rate >= (select max(refund_rate) from tbl);