Running Revenue Totals with Year-over-Year Comparison

Decomposition

• Core metric: Running total of revenue, running total of customer count
• Dimensions: Week
• Time window: Current year and prior year for comparison
• Edge cases: Weeks with no orders, partial weeks (current week), alignment of “same week last year”

Data Needs

• Timestamps: order_date, transaction_date
• Numerical: revenue amount, order_id or customer_id
• Derived: week number, year, cumulative sums
• Comparison: Same week from prior year (week number matching)

Assumed Schema

orders: order_id, customer_id, order_date, revenue

Table Mapping

• Primary table: orders - Contains order dates, revenue, and customer IDs
• Aggregation strategy: Group by week, calculate running totals using window functions
• Comparison logic: Self-join or UNION ALL to align current year weeks with prior year weeks

Join Logic

• No joins needed for basic query structure
• Use window functions (SUM OVER) for running totals
• Use self-join or separate CTEs to compare current year vs prior year

SQL Query

WITH weekly_metrics AS (
  SELECT
    DATE_TRUNC('week', order_date) AS week_start,
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(WEEK FROM order_date) AS week_number,
    SUM(revenue) AS week_revenue,
    COUNT(DISTINCT customer_id) AS week_customers
  FROM orders
  WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
  GROUP BY DATE_TRUNC('week', order_date), EXTRACT(YEAR FROM order_date), EXTRACT(WEEK FROM order_date)
),

running_totals AS (
  SELECT
    week_start,
    year,
    week_number,
    week_revenue,
    week_customers,
    SUM(week_revenue) OVER (
      PARTITION BY year 
      ORDER BY week_number 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    SUM(week_customers) OVER (
      PARTITION BY year 
      ORDER BY week_number 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_customers
  FROM weekly_metrics
),

current_year AS (
  SELECT
    week_start,
    week_number,
    week_revenue,
    week_customers,
    cumulative_revenue,
    cumulative_customers
  FROM running_totals
  WHERE year = EXTRACT(YEAR FROM CURRENT_DATE)
),

prior_year AS (
  SELECT
    week_number,
    cumulative_revenue AS prior_year_cumulative_revenue,
    cumulative_customers AS prior_year_cumulative_customers
  FROM running_totals
  WHERE year = EXTRACT(YEAR FROM CURRENT_DATE) - 1
)

SELECT
  cy.week_start,
  cy.week_number,
  cy.cumulative_revenue AS current_year_revenue_ytd,
  cy.cumulative_customers AS current_year_customers_ytd,
  py.prior_year_cumulative_revenue AS prior_year_revenue_ytd,
  py.prior_year_cumulative_customers AS prior_year_customers_ytd,
  ROUND(
    100.0 * (cy.cumulative_revenue - COALESCE(py.prior_year_cumulative_revenue, 0)) / 
    NULLIF(py.prior_year_cumulative_revenue, 0), 
    1
  ) AS revenue_yoy_percent_change,
  ROUND(
    100.0 * (cy.cumulative_customers - COALESCE(py.prior_year_cumulative_customers, 0)) / 
    NULLIF(py.prior_year_cumulative_customers, 0), 
    1
  ) AS customers_yoy_percent_change
FROM current_year cy
LEFT JOIN prior_year py ON cy.week_number = py.week_number
ORDER BY cy.week_number;

Interview Talking Points

Opening (30 seconds):

“I’ll build this in four steps using CTEs. First, aggregate orders by week and year to get weekly revenue and customer counts. Second, calculate running totals within each year using window functions. Third, separate current year and prior year into their own CTEs. Finally, join on week number to compare same week across years.”

Key decisions:

• Window function for running totals: SUM() OVER (PARTITION BY year ORDER BY week_number) ensures cumulative sums reset each year
• LEFT JOIN from current to prior: Preserves current year weeks even if prior year data is missing (e.g., company didn’t exist, or current week number doesn’t exist in prior year yet)
• Week number matching: Using EXTRACT(WEEK FROM order_date) to align “week 15 this year” with “week 15 last year”
• COALESCE and NULLIF: Handles division by zero and missing prior year data gracefully
• COUNT(DISTINCT customer_id): Running total of unique customers who have ordered by that week (note: this counts cumulative unique customers, not new customers)

Edge case handling:

• Partial current week: Included in running total even if week isn’t complete
• Missing prior year weeks: LEFT JOIN ensures current year data shows even without comparison
• Division by zero: NULLIF prevents errors when prior year revenue is zero

Verification:

“Spot-check: Pick week 10, manually sum revenue from weeks 1-10 for both years, verify it matches the cumulative columns. Also check that week_number alignment makes sense (ISO week vs calendar week).”

Potential extensions:

• Filter to only completed weeks: Add WHERE week_start < DATE_TRUNC('week', CURRENT_DATE)
• Change from week to month: Replace DATE_TRUNC('week', ...) with DATE_TRUNC('month', ...)
• Add week-over-week growth: Join each week to the previous week instead of prior year
• Separate new vs returning customers: Add logic to identify first-time buyers vs repeat