Interview Question Bank · Spend to date · inclusive day counts · pacing ratio
9. Budget pacing as of a report date
HardMarketingQuestion #177
Concept: Spend to date · inclusive day counts · pacing ratioshow
Pacing compares how much of the budget is gone with how much of the flight is gone: (spend_to_date / budget) / (days_elapsed / flight_days). Both day counts include the first and last day, so add 1: end_date - start_date + 1. When the data already holds later days, sum only up to the report date, and LEFT JOIN the spend so a campaign that hasn't spent yet shows 0 rather than vanishing.
Example (different data):
WITH used AS (SELECT project_id, SUM(hours) AS hours FROM timesheets WHERE work_date <= DATE '2025-06-30' GROUP BY project_id) SELECT p.name, COALESCE(u.hours, 0) / p.budget_hours AS budget_used, (DATE '2025-06-30' - p.kickoff + 1)::numeric / (p.deadline - p.kickoff + 1) AS time_used FROM projects p LEFT JOIN used u ON u.project_id = p.id;
Your task
Larkspur Roasters reviews budget pacing every Friday. This week's review is for Friday 2025-03-14, but the stats table has since been loaded with later days too.
For campaigns with a fixed flight (end_date not NULL) that are live on 2025-03-14 (start_date <= that day <= end_date), return name, spend_to_date (spend on or before 2025-03-14, 0 if none), pct_budget_spent (100 * spend_to_date / budget) and pct_flight_elapsed (100 * days from start_date through 2025-03-14, both inclusive / flight days, both inclusive), each rounded to 1 decimal, and pace_status: 'overpacing' if the unrounded pacing ratio (share of budget spent / share of flight elapsed) is above 1.1, 'underpacing' if below 0.9, otherwise 'on pace'. Sort by pct_budget_spent descending, then name.
Starting Postgres in your browser…
Checking your access…