[Solved] Consider the table orders(order_id, customer_id, order_date, amount) with data:(1, 101, '2024-01-01', 100), (2, 101, '2024-01-02', 150), (3, 102, '2024-01-01', 200)What does this query return?... | PostgreSQL

PostgreSQL - Window Functions in PostgreSQL

Consider the table orders(order_id, customer_id, order_date, amount) with data:
(1, 101, '2024-01-01', 100), (2, 101, '2024-01-02', 150), (3, 102, '2024-01-01', 200)
What does this query return?

SELECT order_id, customer_id, amount, LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount FROM orders ORDER BY order_id;

A(1, 101, 100, 150), (2, 101, 150, 100), (3, 102, 200, 0)

B(1, 101, 100, 0), (2, 101, 150, 100), (3, 102, 200, 0)

C(1, 101, 100, 100), (2, 101, 150, 150), (3, 102, 200, 200)

D(1, 101, 100, NULL), (2, 101, 150, 100), (3, 102, 200, NULL)

Step-by-Step Solution

Solution:

Step 1: Understand LAG() function with default value
LAG(amount, 1, 0) returns previous row's amount per customer ordered by date; if none, returns 0.
Step 2: Apply per customer
Customer 101: first order prev_amount=0, second order prev_amount=100; Customer 102: only one order, prev_amount=0.
Final Answer:
(1, 101, 100, 0), (2, 101, 150, 100), (3, 102, 200, 0) -> Option B
Quick Check:
LAG() with default returns previous row or default [OK]

Quick Trick: LAG() returns previous row value or default if none [OK]

Common Mistakes:

Assuming LAG() returns NULL by default
Not partitioning by customer_id
Mixing up order of rows

Master "Window Functions in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Consider the table orders(order_id, customer_id, order_date, amount) with data:

Step 1: Understand LAG() function with default value

Step 2: Apply per customer

Final Answer:

Quick Check:

Want More Practice?