Calculate a 2-day moving average of sales amounts.

Problem Statement

<p>Calculate a 2-day moving average of <code>sales</code> amounts.</p>

Examples

Input: sales table: +------------+--------+ | sale_date | amount | +------------+--------+ | 2024-01-01 | 100 | | 2024-01-02 | 200 | | 2024-01-03 | 150 | | 2024-01-04 | 300 | | 2024-01-05 | 250 | +------------+--------+

Output: +--------------------------+--------+----------------------+ | sale_date | amount | moving_avg | +--------------------------+--------+----------------------+ | 2024-01-01T00:00:00.000Z | 100 | 100.0000000000000000 | | 2024-01-02T00:00:00.000Z | 200 | 150.0000000000000000 | | 2024-01-03T00:00:00.000Z | 150 | 175.0000000000000000 | | 2024-01-04T00:00:00.000Z | 300 | 225.0000000000000000 | | 2024-01-05T00:00:00.000Z | 250 | 275.0000000000000000 | +--------------------------+--------+----------------------+

Explanation: The records are grouped by category and the aggregate calculation is applied to produce the summary result.

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 "Moving Average (Window Frame)", we query the relational database engine using declarative SQL. The goal is to calculate a 2-day moving average of sales amounts. 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, AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg 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.

Moving Average (Window Frame)

Hard

Calculate a 2-day moving average of sales amounts.

Example Scenarios

1Example 1

Input:

sales table

sale_date	amount
2024-01-01	100
2024-01-02	200
2024-01-03	150
2024-01-04	300
2024-01-05	250

Output:

sale_date	amount	moving_avg
2024-01-01T00:00:00.000Z	100	100.0000000000000000
2024-01-02T00:00:00.000Z	200	150.0000000000000000
2024-01-03T00:00:00.000Z	150	175.0000000000000000
2024-01-04T00:00:00.000Z	300	225.0000000000000000
2024-01-05T00:00:00.000Z	250	275.0000000000000000

Explanation:

The records are grouped by category and the aggregate calculation is applied to produce the summary result.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.