Advanced SQL Coding Challenges

Write a Query Using Window Functions

Create a query that calculates the cumulative sum of sales per month for each product.

Expected output: A result set showing cumulative sales per product per month.

Hint: Use SUM() OVER() with PARTITION BY and ORDER BY clauses.

Optimize a Slow Query

Given a query joining three large tables, identify and apply at least two optimization techniques.

Expected output: An optimized query with improved execution time.

Hint: Check indexes, avoid SELECT *, and filter early.

A correlated subquery references columns from the outer query and executes once per row, while a regular subquery executes independently and once.

Window functions are used when you need to perform calculations across rows without collapsing the result set, unlike GROUP BY which aggregates rows into fewer rows.

By using indexes, avoiding SELECT *, filtering early with WHERE clauses, analyzing execution plans, and rewriting inefficient queries.