Why window functions are needed in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the running total of sales.

SQL

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

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

ADESC

BGROUP BY

CASC

DHAVING

Attempts:

3 left

💡 Hint

Common Mistakes

Using DESC will reverse the order and give a running total backward.

Using GROUP BY or HAVING here is incorrect because we want a window function.

The running total should be ordered from earliest to latest date, so we use ASC.

2fill in blank

medium

Complete the code to find the average salary per department without collapsing rows.

SQL

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

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

Adepartment_id

Bemployee_id

Csalary

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by employee_id will give average salary per employee, which is just the salary itself.

Partitioning by salary or hire_date does not group by department.

We partition by department_id to calculate average salary per department while keeping all rows.

3fill in blank

hard

Fix the error in the code to rank employees by salary within each department.

SQL

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

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

Aemployee_id

Bsalary

Cdepartment_id

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by employee_id or department_id does not rank salaries.

Ordering by hire_date ranks by date, not salary.

Ranking should be based on salary, so ORDER BY salary is correct.

4fill in blank

hard

Fill both blanks to calculate the difference between each sale and the previous sale.

SQL

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

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

Asales_amount

Bsales_date

Cemployee_id

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee_id or department_id in LAG or ORDER BY will not give correct previous sale.

Not ordering by sales_date will give wrong previous row.

LAG(sales_amount) gets the previous sale amount ordered by sales_date.

5fill in blank

hard

Fill all three blanks to assign a row number to each employee per department ordered by hire date.

SQL

SELECT employee_id, department_id, hire_date, ROW_NUMBER() OVER (PARTITION BY [1] ORDER BY [2] [3]) AS row_num FROM employees;

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

Adepartment_id

Bhire_date

CASC

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using DESC will number from latest to earliest hire date.

Partitioning by hire_date or employee_id is incorrect for grouping by department.

Partition by department_id to group employees, order by hire_date ascending to number from earliest hire.