Optimize Complex Query for Compute Efficiency

HardSQL · PostgreSQL

Problem

A data analytics team at Acme Corp is facing high compute costs due to a complex SQL query that aggregates sales data across multiple tables. Write a SQL query that optimizes the original query to minimize resource consumption while still delivering the required results.

Requirements

Use CTEs to break down the query into manageable parts.
Optimize JOIN operations to reduce data volume.
Ensure the final result set includes total sales by category and month.
Use window functions to calculate the running total of sales per category.

Output details

Return columns in this order: category_name, month, total_sales, running_total.
Sort the results by month ascending, then category_name ascending.
Calculate running_total across all categories in month order, rather than separately for each category.

Schema

sales

Column	Type	Description
idPK	INT	Primary key
category_id	INT	Foreign key referencing categories
amount	DECIMAL(10,2)	Transaction amount
transaction_date	DATE	Date of transaction

Requirements

Use CTEs to break down the query into manageable parts.
Optimize JOIN operations to reduce data volume.
Ensure the final result set includes total sales by category and month.
Use window functions to calculate the running total of sales per category.

Output details

Return columns in this order: category_name, month, total_sales, running_total.
Sort the results by month ascending, then category_name ascending.
Calculate running_total across all categories in month order, rather than separately for each category.

Column	Type	Description
idPK	INT	Primary key
name	VARCHAR(255)	Customer name
signup_date	DATE	Date of customer signup