Lifted from: Timmy Kurian’s Linkedin

Problem:

Given a table of customer purchases, write a query to calculate the running total of the total purchase amount for each customer, sorted in descending order by the running total.

Sample Tables (CREATE + INSERT):
CREATE TABLE purchases (
 customer_id INT,
 purchase_id INT,
 purchase_amount DECIMAL(10,2),
 purchase_date DATE
);
INSERT INTO purchases (customer_id, purchase_id, purchase_amount, purchase_date)
VALUES
 (1, 1, 50.00, '2020-01-01'),
 (1, 2, 75.00, '2020-01-05'),
 (2, 3, 100.00, '2020-01-10'),
 (2, 4, 25.00, '2020-01-15'),
 (3, 5, 200.00, '2020-01-20'),
 (3, 6, 50.00, '2020-01-25');

Query:

SELECT
 customer_id,
 purchase_id,
 purchase_amount,
 SUM(purchase_amount) OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) AS running_total
FROM purchases
ORDER BY running_total DESC;

Explanation:

This query uses the window function SUM() and OVER() to calculate the running total of the purchase amounts for each customer, sorted in descending order by the purchase date. This allows us to see the total amount spent by each customer over time. The PARTITION BY clause ensures that the running total is calculated separately for each customer. Finally, the ORDER BY clause sorts the results in descending order by the running total. This query can be useful for analyzing customer spending patterns and identifying top customers.