Optimize Complex Query for Compute Efficiency | Dataford Interview Questions

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.

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

categories

Column	Type	Description
idPK	INT	Primary key
name	VARCHAR(255)	Category name

customers

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

Your question is Optimize Complex Query for Compute Efficiency. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem