LAG function for previous row access in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LAG function do in SQL?

The LAG function lets you look at the value from a previous row in the same result set without using a self-join. It helps compare current row data with previous row data easily.

beginner

Write the basic syntax of the LAG function.

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column) Here, offset is how many rows back to look (default 1), and default_value is what to return if no previous row exists.

intermediate

Why do we use the OVER clause with LAG?

The OVER clause defines how to group and order rows for the LAG function. It tells SQL which rows to compare and in what order, so it knows which previous row to look at.

beginner

What happens if there is no previous row when using LAG?

If there is no previous row, LAG returns NULL by default or the specified default value if provided. This prevents errors and lets you handle edge cases.

beginner

Give a real-life example where LAG is useful.

LAG is useful to compare sales of a product this month with last month. For example, you can find the difference in sales between current and previous month to see growth or decline.

What does the LAG function return by default if there is no previous row?

A NULL

B 0

C An error

D The current row value

By default, LAG returns NULL if there is no previous row to look back to.

Which clause is required to specify the order of rows for the LAG function?

A GROUP BY

B ORDER BY inside OVER()

C WHERE

D HAVING

The ORDER BY clause inside the OVER() clause defines the order of rows for LAG to look back.

What is the default offset value for LAG if not specified?

A 0

B 2

C 1

D NULL

The default offset for LAG is 1, meaning it looks at the immediately previous row.

Which of these is a valid use of LAG?

A To get the previous row's value

B To filter rows

C To sum all rows

D To get the next row's value

LAG is used to access the previous row's value in the result set.

Can LAG be used without the OVER() clause?

A Only with GROUP BY

B Yes, always

C Only in MySQL

D No, OVER() is required

LAG is a window function and requires the OVER() clause to define the window.

Explain how the LAG function works and why it is useful.

Think about comparing current data with past data in a list.

Describe the syntax of the LAG function and the role of each part.

Break down the function into its parameters and the window definition.