Show each sale amount and the previous sale amount using LAG.

Problem Statement

<p>Show each sale amount and the previous sale amount using LAG.</p>

Examples

Input: sales table: +----+------------+--------+ | id | sale_date | amount | +----+------------+--------+ | 1 | 2024-01-01 | 100 | | 2 | 2024-01-02 | 150 | | 3 | 2024-01-03 | 200 | +----+------------+--------+

Output: +--------------------------+--------+-------------+ | sale_date | amount | prev_amount | +--------------------------+--------+-------------+ | 2024-01-01T00:00:00.000Z | 100 | null | | 2024-01-02T00:00:00.000Z | 150 | 100 | | 2024-01-03T00:00:00.000Z | 200 | 150 | +--------------------------+--------+-------------+

Explanation: The window function evaluates the calculation across the partition in the specified ordering.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Use a window function with OVER (...) to compute values across rows related to the current row without collapsing them like GROUP BY. 💡 Hint 2: Check if PARTITION BY is required to split windows by category or if only an ORDER BY inside OVER (...) is needed. 💡 Hint 3: Structure the query as: <window_func>() OVER (PARTITION BY ... ORDER BY ...) AS <alias>;

Editorial & Approach

Problem Overview & Intuition

To solve "Window Function: LAG", we query the relational database engine using declarative SQL. The goal is to show each sale amount and the previous sale amount using lag. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply row projections to isolate the requested data.
Format & Order: Sort the resulting records according to specified order criteria.

Optimal Implementation (SQL)

SELECT sale_date, amount, LAG(amount) OVER (ORDER BY sale_date) AS prev_amount FROM sales;

Complexity Analysis

Time Complexity O(N log N) for sorting or partitioning rows.

Space Complexity O(N) for intermediate group hash tables or window buffers.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Window Function: LAG

Medium

Show each sale amount and the previous sale amount using LAG.

Example Scenarios

1Example 1

Input:

sales table

id	sale_date	amount
1	2024-01-01	100
2	2024-01-02	150
3	2024-01-03	200

Output:

sale_date	amount	prev_amount
2024-01-01T00:00:00.000Z	100	null
2024-01-02T00:00:00.000Z	150	100
2024-01-03T00:00:00.000Z	200	150

Explanation:

The window function evaluates the calculation across the partition in the specified ordering.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.