0
0
SQLquery~20 mins

LAG function for previous row access in SQL - Practice Problems & Coding Challenges

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Challenge - 5 Problems
🎖️
LAG Function Mastery
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate
2:00remaining
Output of LAG function with partition

Consider a sales table with columns region, month, and sales. What is the output of the following query?

SELECT region, month, sales, LAG(sales) OVER (PARTITION BY region ORDER BY month) AS prev_sales FROM sales ORDER BY region, month;
SQL
CREATE TABLE sales (region VARCHAR(10), month INT, sales INT);
INSERT INTO sales VALUES
('East', 1, 100),
('East', 2, 150),
('West', 1, 200),
('West', 2, 180);

SELECT region, month, sales, LAG(sales) OVER (PARTITION BY region ORDER BY month) AS prev_sales FROM sales ORDER BY region, month;
A[{"region": "East", "month": 1, "sales": 100, "prev_sales": 150}, {"region": "East", "month": 2, "sales": 150, "prev_sales": 100}, {"region": "West", "month": 1, "sales": 200, "prev_sales": 180}, {"region": "West", "month": 2, "sales": 180, "prev_sales": 200}]
B[{"region": "East", "month": 1, "sales": 100, "prev_sales": null}, {"region": "East", "month": 2, "sales": 150, "prev_sales": 100}, {"region": "West", "month": 1, "sales": 200, "prev_sales": null}, {"region": "West", "month": 2, "sales": 180, "prev_sales": 200}]
C[{"region": "East", "month": 1, "sales": 100, "prev_sales": null}, {"region": "East", "month": 2, "sales": 150, "prev_sales": null}, {"region": "West", "month": 1, "sales": 200, "prev_sales": null}, {"region": "West", "month": 2, "sales": 180, "prev_sales": null}]
D[{"region": "East", "month": 1, "sales": 100, "prev_sales": 100}, {"region": "East", "month": 2, "sales": 150, "prev_sales": 150}, {"region": "West", "month": 1, "sales": 200, "prev_sales": 200}, {"region": "West", "month": 2, "sales": 180, "prev_sales": 180}]
Attempts:
2 left
💡 Hint

Remember that LAG returns the value from the previous row within the same partition ordered by the specified column.

🧠 Conceptual
intermediate
2:00remaining
Understanding default value in LAG function

What will be the output of the prev_sales column in the following query if the default value is set to 0?

SELECT month, sales, LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_sales FROM sales ORDER BY month;
SQL
CREATE TABLE sales (month INT, sales INT);
INSERT INTO sales VALUES
(1, 100),
(2, 150),
(3, 200);

SELECT month, sales, LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_sales FROM sales ORDER BY month;
A[{"month": 1, "sales": 100, "prev_sales": 0}, {"month": 2, "sales": 150, "prev_sales": 100}, {"month": 3, "sales": 200, "prev_sales": 150}]
B[{"month": 1, "sales": 100, "prev_sales": null}, {"month": 2, "sales": 150, "prev_sales": 100}, {"month": 3, "sales": 200, "prev_sales": 150}]
C[{"month": 1, "sales": 100, "prev_sales": 100}, {"month": 2, "sales": 150, "prev_sales": 150}, {"month": 3, "sales": 200, "prev_sales": 200}]
D[{"month": 1, "sales": 100, "prev_sales": 100}, {"month": 2, "sales": 150, "prev_sales": null}, {"month": 3, "sales": 200, "prev_sales": 150}]
Attempts:
2 left
💡 Hint

The third argument in LAG is the default value used when there is no previous row.

📝 Syntax
advanced
2:00remaining
Identify the syntax error in LAG usage

Which option contains a syntax error in the use of the LAG function?

SELECT id, value, LAG(value 1) OVER (ORDER BY id) AS prev_val FROM data;
SQL
CREATE TABLE data (id INT, value INT);
INSERT INTO data VALUES (1, 10), (2, 20);

SELECT id, value, LAG(value 1) OVER (ORDER BY id) AS prev_val FROM data;
ALAG(value, 1, 0) OVER (ORDER BY id)
BLAG(value, 1) OVER (ORDER BY id)
CLAG(value 1) OVER (ORDER BY id)
DLAG(value) OVER (ORDER BY id)
Attempts:
2 left
💡 Hint

Check the commas between function arguments.

❓ optimization
advanced
2:00remaining
Optimizing LAG usage with large datasets

You have a large table with millions of rows. Which approach is best to optimize the performance of a query using LAG?

AUse <code>LAG</code> with a proper <code>PARTITION BY</code> and <code>ORDER BY</code> to limit the window size.
BReplace <code>LAG</code> with a self-join on the table without indexes.
CUse <code>LAG</code> without <code>PARTITION BY</code> to process all rows at once.
DRemove the <code>ORDER BY</code> clause inside the <code>OVER</code> clause to speed up the query.
Attempts:
2 left
💡 Hint

Think about how window functions process data partitions.

🔧 Debug
expert
3:00remaining
Debugging unexpected NULLs in LAG output

A query uses LAG(sales) OVER (ORDER BY month) but unexpectedly returns NULL for some rows that are not the first row. What is the most likely cause?

AThe query is missing a <code>PARTITION BY</code> clause.
BThe <code>LAG</code> function is missing the default value argument.
CThe table has NULL values in the <code>sales</code> column.
DThe <code>month</code> column has duplicate values causing ties in ordering.
Attempts:
2 left
💡 Hint

Consider how ordering affects which row is considered previous.