OVER clause with PARTITION BY in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the total sales per department using the OVER clause with PARTITION BY.

SQL

SELECT department, sales, SUM(sales) OVER ([1] department) AS total_sales FROM sales_data;

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

AORDER BY

BGROUP BY

CPARTITION BY

DWHERE

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside the OVER clause causes syntax errors.

Confusing ORDER BY with PARTITION BY inside OVER.

The OVER clause with PARTITION BY divides the rows into partitions to calculate the sum per department.

2fill in blank

medium

Complete the code to rank employees by salary within each department using the RANK() function and PARTITION BY.

SQL

SELECT employee_id, department, salary, RANK() OVER ([1] department ORDER BY salary DESC) AS salary_rank FROM employees;

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

AGROUP BY

BWHERE

CORDER BY

DPARTITION BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER instead of PARTITION BY.

Omitting PARTITION BY causes ranking over the entire table.

The RANK() function uses OVER (PARTITION BY ... ORDER BY ...) to rank salaries within each department.

3fill in blank

hard

Fix the error in the code to calculate the cumulative sales per region using SUM() with OVER and PARTITION BY.

SQL

SELECT region, sales, SUM(sales) OVER (PARTITION BY [1] ORDER BY sales) AS cumulative_sales FROM sales_data;

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

Aregion

Bsales

Cdepartment

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using the sales column in PARTITION BY instead of region.

Omitting PARTITION BY causes cumulative sum over entire table.

The PARTITION BY clause should use the column that groups the data, here region, to calculate cumulative sales per region.

4fill in blank

hard

Fill both blanks to calculate the average salary per department and order the results by department.

SQL

SELECT department, AVG(salary) OVER ([1] department [2] salary) AS avg_salary FROM employees;

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

APARTITION BY

BORDER BY

CGROUP BY

DWHERE

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER causes syntax errors.

Omitting ORDER BY changes the window function behavior.

The PARTITION BY clause groups rows by department, and ORDER BY orders rows within each partition by salary.

5fill in blank

hard

Fill all three blanks to calculate the running total of sales per store, ordered by date.

SQL

SELECT store_id, sales_date, sales, SUM(sales) OVER ([1] store_id [2] sales_date [3] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales_records;

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

APARTITION BY

BORDER BY

CROWS

DGROUP BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER instead of PARTITION BY.

Omitting ROWS clause changes the window frame behavior.

The PARTITION BY groups rows by store, ORDER BY sorts by sales_date, and ROWS defines the frame for the running total.