LAG and LEAD for row comparison in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to get the previous salary of each employee using LAG.

PostgreSQL

SELECT employee_id, salary, LAG(salary) OVER (ORDER BY employee_id) AS previous_salary FROM employees ORDER BY employee_id [1];

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

ADESC

BASC

CGROUP BY

DLIMIT

Attempts:

3 left

💡 Hint

Common Mistakes

Using DESC order will reverse the order and give wrong previous rows.

Using GROUP BY instead of ORDER BY causes syntax errors.

The LAG function looks at the previous row based on the order. We order by employee_id ascending to get the previous salary correctly.

2fill in blank

medium

Complete the code to get the next salary of each employee using LEAD.

PostgreSQL

SELECT employee_id, salary, LEAD(salary) OVER (ORDER BY employee_id) AS next_salary FROM employees ORDER BY employee_id [1];

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

AGROUP BY

BHAVING

CDESC

DASC

Attempts:

3 left

💡 Hint

Common Mistakes

Using DESC order reverses the order and gives wrong next rows.

Using GROUP BY or HAVING causes syntax errors here.

The LEAD function looks at the next row based on the order. We order by employee_id ascending to get the next salary correctly.

3fill in blank

hard

Fix the error in the code to correctly get the previous and next salaries.

PostgreSQL

SELECT employee_id, salary, LAG(salary) OVER (ORDER BY [1]) AS prev_sal, LEAD(salary) OVER (ORDER BY employee_id) AS next_sal FROM employees;

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

Asalary

Bdepartment

Cemployee_id

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using different columns in ORDER BY for LAG and LEAD causes mismatched rows.

Ordering by salary may not give consistent previous and next rows.

Both LAG and LEAD should use the same ordering column to compare rows properly. Here, employee_id is the correct column to order by.

4fill in blank

hard

Fill both blanks to calculate the salary difference between current and previous employee.

PostgreSQL

SELECT employee_id, salary, salary - [1](salary) OVER (ORDER BY employee_id) AS salary_diff FROM employees ORDER BY [2] employee_id;

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

ALAG

BLEAD

CASC

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEAD instead of LAG gives wrong difference direction.

Ordering DESC reverses the sequence and causes wrong differences.

Use LAG to get the previous salary and subtract it from current salary. Order by employee_id ASC to get correct sequence.

5fill in blank

hard

Fill all three blanks to find the percentage change in salary compared to the previous employee.

PostgreSQL

SELECT employee_id, salary, ROUND(((salary - [1](salary) OVER (ORDER BY employee_id)) * 100.0) / [2](salary) OVER (ORDER BY employee_id), 2) AS pct_change FROM employees ORDER BY [3] employee_id;

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

ALAG

CASC

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEAD instead of LAG causes wrong comparisons.

Ordering DESC reverses the order and gives wrong results.

Use LAG to get the previous salary for both subtraction and division. Order by employee_id ASC for correct row sequence. This calculates the percentage change from previous salary.