LAG function for previous row access in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the previous salary using LAG function.

SQL

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

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

A10

B'10'

Cdepartment_id

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around numeric values in WHERE clause.

Using column names instead of values in WHERE clause.

The department_id is a number, so use 10 without quotes to filter correctly.

2fill in blank

medium

Complete the code to get the previous order date for each customer.

SQL

SELECT customer_id, order_date, LAG(order_date) OVER (ORDER BY [1]) AS previous_order FROM orders;

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

Acustomer_id

Border_date

Corder_id

Dorder_status

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by customer_id instead of order_date.

Ordering by unrelated columns.

Ordering by order_date ensures the previous order date is the correct previous row.

3fill in blank

hard

Fix the error in the LAG function usage to get previous sales amount per region.

SQL

SELECT region, sales_date, LAG(sales_amount) OVER (PARTITION BY [1] ORDER BY sales_date) AS prev_sales FROM sales_data;

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

Aemployee_id

Bsales_date

Csales_amount

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by sales_date which is ordering column.

Partitioning by sales_amount which is a value column.

Partitioning by region groups rows by region to get previous sales within each region.

4fill in blank

hard

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

SQL

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

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

Adepartment_id

Bsalary

Cemployee_id

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by salary which may not reflect sequence.

Partitioning by employee_id which is unique per row.

Partition by department_id to group employees by department. Order by hire_date to get previous salary in hire order.

5fill in blank

hard

Fill all three blanks to get previous and next order dates per customer and calculate gap days.

SQL

SELECT customer_id, order_date, LAG(order_date) OVER (PARTITION BY [1] ORDER BY [2]) AS prev_order, LEAD(order_date) OVER (PARTITION BY [1] ORDER BY [2]) AS next_order, DATEDIFF(day, LAG(order_date) OVER (PARTITION BY [1] ORDER BY [2]), order_date) AS days_since_prev FROM orders;

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

Acustomer_id

Border_date

Corder_id

Dsales_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by order_id which is unique per order.

Ordering by sales_amount which is unrelated to order sequence.

Using different columns for LAG and LEAD ordering.

Partition by customer_id to group orders per customer. Order by order_date to get previous and next orders chronologically. Use order_date in DATEDIFF to calculate days difference.