Show total sales per year and the previous year total alongside each.

Problem Statement

<p>Show total <code>sales</code> per year and the previous year total alongside each.</p>

Examples

Input: sales table: +------------+--------+ | sale_date | amount | +------------+--------+ | 2022-03-01 | 100 | | 2022-06-15 | 200 | | 2023-02-10 | 150 | | 2023-09-20 | 300 | | 2024-01-05 | 250 | +------------+--------+

Output: +------+-------+-----------------+ | yr | total | prev_year_total | +------+-------+-----------------+ | 2022 | 300 | null | | 2023 | 450 | 300 | | 2024 | 250 | 450 | +------+-------+-----------------+

Explanation: The query retrieves the requested records satisfying all problem requirements.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Identify the grouping dimension(s) and which columns require aggregate functions (such as COUNT, SUM, AVG, MIN, or MAX). 💡 Hint 2: Add the GROUP BY clause for all non-aggregated columns listed in the SELECT projection. 💡 Hint 3: If filtering groups, use HAVING; otherwise use WHERE before grouping: SELECT <group_col>, <AGG>(...) FROM <table> GROUP BY <group_col>;

Editorial & Approach

Problem Overview & Intuition

To solve "Year-over-Year Comparison", we query the relational database engine using declarative SQL. The goal is to show total sales per year and the previous year total alongside each. 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 grouping & aggregates to isolate the requested data.
Format & Order: Sort the resulting records according to specified order criteria.

Optimal Implementation (SQL)

WITH yearly AS (SELECT EXTRACT(YEAR FROM sale_date) AS yr, SUM(amount) AS total FROM sales GROUP BY EXTRACT(YEAR FROM sale_date)) SELECT yr, total, LAG(total) OVER (ORDER BY yr) AS prev_year_total FROM yearly;

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.

Year-over-Year Comparison

Hard

Show total sales per year and the previous year total alongside each.

Example Scenarios

1Example 1

Input:

sales table

sale_date	amount
2022-03-01	100
2022-06-15	200
2023-02-10	150
2023-09-20	300
2024-01-05	250

Output:

yr	total	prev_year_total
2022	300	null
2023	450	300
2024	250	450

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.