OVER clause with ORDER BY in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

SQL

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

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

Aamount

Bsales_date

Ccustomer_id

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by amount instead of date changes the running total order.

Using a column unrelated to ordering like customer_id causes wrong results.

The OVER clause with ORDER BY sales_date calculates the running total in date order.

2fill in blank

medium

Complete the code to rank employees by their salary within each department.

SQL

SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY [1] DESC) AS dept_rank FROM employees;

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

Asalary

Bhire_date

Cdepartment_id

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by employee_id or department_id instead of salary.

Not using DESC to rank highest salary first.

The RANK() function ranks employees by salary descending within each department.

3fill in blank

hard

Fix the error in the code to calculate the cumulative average salary ordered by hire date.

SQL

SELECT employee_id, salary, AVG(salary) OVER (ORDER BY [1] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_avg_salary FROM employees;

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

Ahire_date

Bsalary

Cemployee_id

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by salary causes incorrect cumulative average.

Using employee_id or department_id for ordering is incorrect here.

The cumulative average should be ordered by hire_date to calculate average salary up to each employee's hire date.

4fill in blank

hard

Fill both blanks to calculate the difference between each sale amount and the previous sale amount ordered by sales_date.

SQL

SELECT sales_date, amount, amount - LAG(amount) OVER (ORDER BY [1] [2]) AS diff FROM sales;

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

Asales_date

BASC

CDESC

Damount

Attempts:

3 left

💡 Hint

Common Mistakes

Using DESC reverses the order and changes the meaning of previous row.

Ordering by amount instead of sales_date gives wrong previous row.

The LAG() function looks at the previous row ordered by sales_date ASC to find the difference.

5fill in blank

hard

Fill all three blanks to calculate the cumulative sum of sales amount partitioned by region and ordered by sales_date.

SQL

SELECT region, sales_date, amount, SUM(amount) OVER (PARTITION BY [1] ORDER BY [2] [3]) AS cum_sum FROM sales;

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

Aregion

Bsales_date

CASC

Damount

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by sales_date or amount is incorrect.

Ordering by amount or descending order changes the cumulative sum meaning.

The cumulative sum is partitioned by region and ordered by sales_date ASC to sum sales in date order per region.