PARTITION BY for grouping windows in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the total sales for each department.

PostgreSQL

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

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

APARTITION BY department ORDER BY date

BPARTITION BY department

CORDER BY department

DGROUP BY department

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside the OVER() clause causes syntax errors.

Omitting PARTITION BY calculates the sum over the entire table, not per department.

The PARTITION BY clause groups rows by department for the window function. Here, SUM(sales) OVER (PARTITION BY department) calculates the total sales per department.

2fill in blank

medium

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

PostgreSQL

SELECT employee_id, department, salary, RANK() OVER ([1]) AS dept_rank FROM employees;

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

APARTITION BY department ORDER BY salary DESC

BGROUP BY department ORDER BY salary DESC

CPARTITION BY salary ORDER BY department

DORDER BY department, salary DESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER() causes errors.

Omitting ORDER BY inside OVER() results in all ranks being 1.

The PARTITION BY department ORDER BY salary DESC clause ranks employees within each department by descending salary.

3fill in blank

hard

Fix the error in the window function to calculate the average salary per department.

PostgreSQL

SELECT department, employee_id, salary, AVG(salary) OVER ([1]) AS avg_dept_salary FROM employees;

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

APARTITION BY department

BPARTITION BY department ORDER BY employee_id

CORDER BY department

DGROUP BY department

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER() causes syntax errors.

Including ORDER BY unnecessarily can change window frame behavior.

For average per department, use PARTITION BY department without ORDER BY. ORDER BY is optional and not needed here.

4fill in blank

hard

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

PostgreSQL

SELECT region, date, sales, SUM(sales) OVER ([1] [2]) AS cumulative_sales FROM sales_records;

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

APARTITION BY region

BORDER BY date

CGROUP BY region

DPARTITION BY date

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER() causes errors.

Omitting ORDER BY results in total sum, not cumulative.

Use PARTITION BY region to group sales by region and ORDER BY date to calculate cumulative sum in date order.

5fill in blank

hard

Fill all three blanks to calculate the row number partitioned by department and ordered by hire_date.

PostgreSQL

SELECT employee_id, department, hire_date, ROW_NUMBER() OVER ([1] [2]) AS row_num FROM employees WHERE salary > [3];

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

APARTITION BY department

BORDER BY hire_date

C50000

DGROUP BY department

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER() causes errors.

Putting salary filter inside OVER() is incorrect.

Use PARTITION BY department and ORDER BY hire_date inside OVER() to number rows per department by hire date. The WHERE clause filters salaries above 50000.