sqlintermediate10 minutes

Create a SQL Function to Calculate Running Sales Total per Customer

Build a SQL function that calculates the running total of sales for each customer ordered by the sale date.

Challenge prompt

Write a SQL function named calculate_running_total that takes no parameters and returns a table showing each customer's sales transactions with a running total of their sales amount ordered by sale_date. The input table 'sales' has columns: customer_id (INT), sale_date (DATE), and amount (DECIMAL). Your function should output columns: customer_id, sale_date, amount, running_total. The running_total is the cumulative sum of the amount for each customer ordered by sale_date.

Guidance

• Use window functions with PARTITION BY to sum the amount per customer.
• Order the running total calculation by sale_date to maintain correct cumulative sums.
• Return the results as a table with all specified columns.

Hints

• Consider using the SQL window function SUM() OVER (PARTITION BY customer_id ORDER BY sale_date).
• Make sure the function returns a table with the correct columns and types.
• Pay attention to the order of rows when calculating the running total.

Starter code

CREATE FUNCTION calculate_running_total()
RETURNS TABLE (
  customer_id INT,
  sale_date DATE,
  amount DECIMAL(10,2),
  running_total DECIMAL(10,2)
) AS $$
BEGIN
  RETURN QUERY
  -- your query here
;
END;
$$ LANGUAGE plpgsql;

Expected output

customer_id | sale_date | amount | running_total ------------|------------|---------|-------------- 1 | 2023-01-01 | 100.00 | 100.00 1 | 2023-01-05 | 50.00 | 150.00 2 | 2023-01-02 | 200.00 | 200.00 2 | 2023-01-10 | 100.00 | 300.00

Core concepts

window functionsrunning totalSQL functionpartition by

Challenge a Friend

Send this duel to someone else and see if they can solve it.