Monthly Running Total

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Problem

We track transaction-level sales and want a per-product ledger where each month shows that month's sales beside a cumulative total that carries forward from the product's earliest month onward. Present it product by product from the lowest id upward, walking each product's months in calendar order, and leave out any transaction that isn't tied to a product.

Cumulative sales per product across months.

Tables

transactions

Columns: transaction_id INTEGER, product_id INTEGER, total_amount REAL, transaction_date TEXT, user_id INTEGER, quantity INTEGER

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Querying transactions for total_amount data using grouping and running total tests whether you can translate a business requirement into the right column references and filter sequence. It shows up in mid-level screens to verify practical fluency.

Break down the requirements

Step 1: Apply the window function

CTE monthly: filter transactions to product_id IS NOT NULL (excludes unattributed rows), then SUM(total_amount) grouped by product_id and the YYYY-MM month bucket.

Step 2: Aggregate with SUM

Outer query: a cumulative window SUM(monthly_total) OVER (PARTITION BY product_id ORDER BY month) accumulates monthly totals across months within each product.

Step 3: Order the final output

ORDER BY product_id, month gives the deterministic by-product-then-month ordering the prose requires.

The solution

WITH monthly AS (
  SELECT
    product_id,
    strftime('%Y-%m', transaction_date) AS month,
    SUM(total_amount) AS monthly_total
  FROM transactions
  WHERE product_id IS NOT NULL
  GROUP BY product_id, strftime('%Y-%m', transaction_date)
)
SELECT
  product_id,
  month,
  monthly_total,
  SUM(monthly_total) OVER (PARTITION BY product_id ORDER BY month) AS cumulative_total
FROM monthly
ORDER BY product_id, month

Common follow-up questions

• If `transactions.transaction_id` could contain unexpected NULL values, how would your query behave? (Tests NULL awareness even when the schema does not currently allow NULLs in `transaction_id`.)
• How would you verify that your aggregation on `transactions.transaction_id` is not double-counting due to duplicate rows? (Tests data quality awareness and deduplication strategies.)
• With millions of distinct values in `transactions.transaction_id`, what index strategy would you use to keep this query performant? (Tests indexing knowledge specific to high-cardinality columns like `transaction_id`.)