FIRST_VALUE and LAST_VALUE in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the first salary value in each department.

PostgreSQL

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

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

AFIRST_VALUE

BLAST_VALUE

CMAX

DMIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LAST_VALUE instead of FIRST_VALUE.

Using aggregate functions like MAX or MIN without window clause.

The FIRST_VALUE function returns the first value in the ordered partition, which here is the lowest salary per department.

2fill in blank

medium

Complete the code to select the last salary value in each department.

PostgreSQL

SELECT department_id, [1](salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary FROM employees;

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

ALAST_VALUE

BFIRST_VALUE

CMAX

DMIN

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the frame clause, which causes LAST_VALUE to return unexpected results.

Using FIRST_VALUE instead of LAST_VALUE.

The LAST_VALUE function returns the last value in the ordered partition. The frame clause ensures it looks at the entire partition.

3fill in blank

hard

Fix the error in the code to correctly get the last salary per department.

PostgreSQL

SELECT department_id, LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary [1]) AS last_salary FROM employees;

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

AROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

BRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

CRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

DROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Attempts:

3 left

💡 Hint

Common Mistakes

Using default frame which ends at current row.

Using RANGE frame which behaves differently.

To get the correct last value over the entire partition, the frame must be set to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Otherwise, LAST_VALUE returns the current row's value.

4fill in blank

hard

Fill both blanks to select the first and last hire dates per department.

PostgreSQL

SELECT department_id, [1](hire_date) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_hire, [2](hire_date) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire FROM employees;

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

AFIRST_VALUE

BMAX

CLAST_VALUE

DMIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using aggregate functions like MAX or MIN instead of window functions.

Omitting the frame clause for LAST_VALUE.

FIRST_VALUE gets the earliest hire date per department, and LAST_VALUE gets the latest hire date with the correct frame clause.

5fill in blank

hard

Fill all three blanks to create a query that shows employee name, first salary, last salary, and the difference between them per department.

PostgreSQL

SELECT employee_name, department_id, [1](salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_salary, [2](salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary, [3] - [2] AS salary_diff FROM employees;

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

AFIRST_VALUE

Blast_salary

Csalary

DLAST_VALUE

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the order of FIRST_VALUE and LAST_VALUE.

Using column names incorrectly in the difference calculation.

The query uses FIRST_VALUE and LAST_VALUE to get the first and last salaries per department. The difference is calculated by subtracting the last salary from the current salary.