Why advanced window functions matter in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the running total of sales ordered by date.

SQL

SELECT date, sales, SUM(sales) OVER (ORDER BY [1]) AS running_total FROM sales_data;

Drag options to blanks, or click blank then click option'

Adate

Bsales

Ccustomer_id

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by sales instead of date causes incorrect running totals.

Using a non-date column for ordering breaks the time sequence.

The running total should be ordered by the date column to accumulate sales over time.

2fill in blank

medium

Complete the code to assign a rank to each employee based on their sales within each region.

SQL

SELECT employee_id, region, sales, RANK() OVER (PARTITION BY [1] ORDER BY sales DESC) AS sales_rank FROM employee_sales;

Drag options to blanks, or click blank then click option'

Aregion

Bsales

Cemployee_id

Ddate

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by employee_id results in each employee being in their own group.

Not partitioning causes ranking across all employees ignoring regions.

Partitioning by region groups employees so ranks are calculated within each region separately.

3fill in blank

hard

Fix the error in the code to calculate the difference between current and previous sales values ordered by date.

SQL

SELECT date, sales, sales - LAG([1]) OVER (ORDER BY date) AS sales_diff FROM sales_data;

Drag options to blanks, or click blank then click option'

Aregion

Bsales

Cdate

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using date inside LAG causes a type error when subtracting.

Using a non-numeric column inside LAG breaks the calculation.

The LAG function should refer to the sales column to get the previous sales value for subtraction.

4fill in blank

hard

Fill both blanks to calculate the cumulative average sales per region ordered by date.

SQL

SELECT region, date, sales, AVG([1]) OVER (PARTITION BY [2] ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg FROM sales_data;

Drag options to blanks, or click blank then click option'

Asales

Bdate

Cregion

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using date instead of sales inside AVG causes wrong results.

Partitioning by date instead of region mixes data groups.

The cumulative average is calculated on the sales column, partitioned by region to group data correctly.

5fill in blank

hard

Fill all three blanks to calculate the difference between current sales and the average sales of the previous 3 days within each region.

SQL

SELECT region, date, sales, sales - AVG([1]) OVER (PARTITION BY [2] ORDER BY [3] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS diff_from_avg_prev_3_days FROM sales_data;

Drag options to blanks, or click blank then click option'

Asales

Bregion

Cdate

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee_id instead of date for ordering breaks the time logic.

Not partitioning by region mixes data across regions.

The average is calculated on sales, partitioned by region, ordered by date to consider the previous 3 days correctly.