Spotify Business Intelligence Analyst Interview Preparation Guide - Mid Level (2-5 years)

Direct answer. Walk through your triage process for narrowing down what changed: what evidence you would gather first, the handful of underlying causes that pattern is usually explained by, and how you would confirm which one actually happened rather than guessing.

Structured elaboration. Start by gathering evidence that distinguishes "the plan changed" from "the plan is the same but the work it does grew": compare the current plan to a known-good baseline if one is available (many engines let you capture and diff plans, or at minimum you can re-run EXPLAIN and eyeball the operator shapes), and check whether the DATA changed (row count growth, a new skewed value, a bulk load) independent of any plan change. From there, the most common root causes for this "regressed with no application change" pattern are, in roughly descending order of how often they turn out to be the actual cause: statistics that went stale after a data change (a bulk load or a big delete that wasn't followed by a refresh); a cached plan compiled for an atypical parameter value now being reused for a much more typical, much less selective one (a parameter-sniffing regression); the DATA itself simply growing or shifting distribution past a threshold that used to favor one strategy and now favors another; and resource contention from something unrelated to this specific query (concurrent load, a maintenance job, disk pressure) that's slowing everything down and only looks query-specific because that's the query someone happened to notice first.

To confirm which one actually happened rather than just picking the most common cause and hoping: check statistics freshness and the estimated-vs-actual row counts at the plan's key nodes first, since that single check distinguishes the stale-statistics and parameter-sniffing causes (both would show a mismatch) from the pure-resource-contention cause (which typically wouldn't); if estimates and actuals both look reasonable and current, pivot to checking system-level resource metrics (CPU, I/O, lock waits) for the affected time window instead.

Worked example. A nightly report that quietly regressed after last week's data load added a disproportionate number of rows in one previously-small category is a textbook case: the statistics captured before the load still describe the smaller, more balanced distribution, so the optimizer's row-count estimate for that category is now badly wrong, and a plan built around that bad estimate (likely favoring an index-heavy strategy suited to the old, smaller volume) is now handling a much larger actual volume than it was ever appropriate for.

Trade-offs and pitfalls. It's tempting to jump straight to "just refresh statistics and see if that fixes it" without confirming that's actually the cause; that's a reasonable first cheap experiment to try, but treat a fix that "seems to help" with some skepticism until you've actually looked at the estimated-vs-actual gap directly, since a coincidental unrelated change (contention easing off on its own) can make an unrelated fix look like it worked.

Direct answer
A strong answer names a specific dashboard, states clearly who the audience was and what business question it answered, walks through the concrete visualization and validation choices made, and ends with a specific decision the dashboard influenced and what changed after stakeholder feedback.

Structured elaboration

• Audience and business question: name the actual stakeholders (e.g. a regional sales leadership team) and the specific question the dashboard needed to answer (e.g. "where is pipeline coverage insufficient for next quarter's target").
• Visualization and validation choices: describe why a particular chart type was chosen over alternatives, and what steps were taken to validate the underlying data (reconciling against a source system, catching an early data-quality issue) before it shipped.
• A decision it influenced: state a specific action taken because of the dashboard (e.g. reallocating budget to an underperforming region, or catching a pricing error before it compounded), not just "it was viewed a lot."
• A change after feedback: describe one concrete iteration (e.g. adding a filter stakeholders repeatedly asked for, or simplifying a chart that was initially too dense) that shows the dashboard evolved based on real usage, not that it was built once and left untouched.

Worked example
A regional sales pipeline dashboard built for a VP of sales: chose a bullet-chart layout for pipeline-coverage-vs-target per region (chosen over a plain bar chart because it needed to show both the actual value and a qualitative threshold band at once); validated the pipeline totals against the CRM's own reporting before launch and caught a stage-mapping bug in the process; the VP used it to reallocate a rep from an over-covered region to an under-covered one the following week; after initial feedback that the page was too dense, cut it from 12 tiles to 6 and added a region filter.

Trade-offs and pitfalls
A generic answer ("I built a dashboard and people liked it") reads as unpracticed; the strongest version of this answer is specific about the CHOICE made and the ALTERNATIVE rejected at each step, not just a description of the finished artifact.

Situation: Multiple PMs each told me their dashboard was top priority and timelines conflicted. As the BI analyst, I needed an objective way to align priorities so engineering and BI capacity went to the highest business impact.

Task: Create an evidence-based prioritization that stakeholders accept and that maps to measurable outcomes.

Action:

• Clarify goals quickly with each PM (what decision the dashboard enables, cadence, audience, SLA).
• Collect objective data points for each request:
  • Business impact proxies: estimated revenue influence, cost savings, churn reduction, or risk reduction (PM-provided or derived from historical correlations).
  • Usage & stakeholder reach: number of users, decision frequency, executive vs. operational.
  • Time sensitivity: how soon decisions must be made (compliance, month-end close).
  • Effort & complexity: estimated dev hours, data readiness, cross-source joins, and maintenance.
  • Observability metrics: expected refresh rate, data latency tolerance, and error-handling needs.
  • Opportunity cost: what other work would be delayed.
• Normalize and score requests using a simple weighted rubric (example weights: Impact 40%, Urgency 25%, Reach 15%, Effort -20%). I implement the scoring in SQL/Excel and share a ranked list with data supporting each score.
• Run a 30-minute alignment workshop showing the rubric, evidence for top candidates (sample SQL counts, user lists, mock KPIs), and propose a phased plan: ship a minimal dashboard for the highest-scoring item, schedule the next per score, and reserve urgent slots for true blockers.
• Offer instrumentation: add usage tracking (dashboard opens, filters used, time-to-insight) and a 3-month review to re-score based on real adoption and impact.

Result: The group accepted the transparent ranking because it surfaced trade-offs with numbers (e.g., Dashboard A scored 82 due to 20 exec users and direct revenue tie vs. Dashboard B scoring 55 despite louder requests). We delivered the top dashboard in two sprints, tracked 70% adoption in key users, and prevented rework by catching a data-quality issue early.

This approach makes prioritization repeatable, defensible, and tied to measurable business outcomes while keeping stakeholders engaged.

Direct answer

One practical way to detect early product-market fit signals is to look at 30-day retention within acquisition-week cohorts: if a meaningful and growing share of each new cohort is still active a month later, and that share holds up or improves as more cohorts are observed, that is a reasonable early heuristic that the product is delivering repeatable value rather than a one-time novelty.

Structured elaboration

The cohort dimension to use is acquisition week, because it lets you compare successive groups of new users on equal footing (same amount of elapsed time since joining) rather than comparing an aggregate metric that mixes users at very different points in their lifecycle. The metric to pair with it is either 30-day retention or the percentage of a cohort completing the product's core action at least once in a defined follow-up window, whichever better reflects genuine repeat value for that specific product.

A simple threshold heuristic: if 30-day retention for successive weekly cohorts is trending upward, or at minimum holding flat above a level the team considers meaningfully better than a typical unengaged baseline for the category, that is treated as an early positive signal. The threshold itself is necessarily product-specific (a reasonable bar for a daily habit product looks nothing like a reasonable bar for an infrequently-used utility), so a team usually calibrates it against comparable products in the same category rather than a universal number.

Worked example

Suppose a new note-taking app tracks 30-day retention for its first six weekly signup cohorts: 8%, 11%, 14%, 13%, 17%, and 19%. Even though each individual number is modest in isolation, the upward trend across six consecutive cohorts, rather than a flat or declining line, is itself informative: it suggests something about the product or its onboarding is genuinely improving cohort quality over time, which is a stronger early signal than any single cohort's absolute retention number. By contrast, six cohorts showing 15%, 12%, 16%, 11%, 14%, 13%, hovering with no clear trend, would be a weaker signal even at a similar average level, since it looks more like noise around a stable (and possibly weak) baseline than evidence of improving fit.

Trade-offs and pitfalls

Early cohorts are small by definition, so a trend across only a handful of weekly cohorts can be noisy; treating six data points as a confirmed trend rather than a suggestive early read risks over-claiming certainty the sample size does not support. It is also easy to conflate a genuinely improving product with an improving ACQUISITION mix (later cohorts skewing toward higher-intent users because of a change in where signups are coming from), so a careful read checks whether the acquisition channel mix has stayed roughly constant across the cohorts being compared before crediting the product itself for the trend.

Direct answer

I treat it as a small, bounded project rather than open-ended study: set an explicit target and timebox up front, decide deliberately what to cover first versus what to defer, and build in hands-on practice from early on instead of finishing all the reading first.

Structured elaboration

Setting the target and timebox: I write down a specific, checkable capability I'm aiming for (not "learn X" but "be able to do Y unsupervised") and a rough deadline, because an open-ended goal never actually finishes.

Deciding what to cover first: I split what's strictly needed for the task in front of me from what's merely good to eventually know, and cover the first category before the second, even if it means leaving obvious gaps for later on purpose.

Hands-on practice over passive consumption: I build something small and real within the first day or two rather than reading everything before touching anything, since reading alone doesn't reveal the parts I don't actually understand. Once the fundamentals feel solid, I deliberately try one piece without a guide, to close the gap between following tutorials and doing genuinely unsupervised work.

Validating before it touches anything real: I check my understanding on a low-stakes copy or sandbox before applying it to live work, the same way I'd validate any other new skill.

Fitting the plan around the rest of the job: a learning plan that assumes a clear runway rarely survives contact with a normal week, so I build it around recurring duties like an on-call rotation rather than pretending they won't interfere.

Making it not evaporate: I keep a short running note of what I learned and where the tricky parts were, mainly so I'm not relearning the same thing from scratch in three months. That note only pays off if it's actually findable later, so I title or tag it by the specific problem it solved, not by the tool's name, since I'm far more likely to remember the problem than the tool's name months later.

Worked example

I once had roughly two weeks to get productive in Terraform, an area outside my usual application-code work, running around an existing on-call rotation rather than a clear runway. The target was specific: be able to make a networking change, adding a new subnet without breaking existing routing, independently by the end of the window. I covered state management and the networking module first, since that was the piece directly blocking the task, deferred the rest of the provider's surface area, and built a small real thing, a test subnet in a sandbox account, after about two days of reading rather than finishing every doc first. I did the mornings before on-call load typically picked up, and validated the work against that sandbox copy before it touched anything live. Afterward I kept a short note titled "subnet sizing and CIDR overlap," the specific problem it solved, and it paid off a few months later when a teammate hit a CIDR overlap while adding a subnet of their own and I found my note in under a minute instead of relearning the whole area.

Trade-offs and pitfalls

The most common failure is spending the whole timebox reading and never building anything, which feels productive but leaves the gaps invisible until they matter. The other is skipping the validation step and discovering the gaps for the first time on something that's already live and real.

Assumptions: baseline contribution margin per ride = fare - driver pay - incentives - variable ops costs. Let baseline fare = F, incentives = I, contribution = C = F - I - others. Suppose ‘others’ fixed proportion unchanged. Numeric intuition: if F increases 3% and I drops 10%. Let F=100, I=20, others=50 => C0 = 100-20-50=30 (30% margin). New F=103, new I=18 (10% drop). C1 =103-18-50=35 => margin increases from 30 to 35 = +16.7% relative uplift in contribution. Key point: percent changes on different bases matter—small percent increase on large fare base yields less absolute than percent drop on smaller incentive pool if incentive was large. Sensitivity: if incentives were only 5, new C=103-4.5-50=48.5 => large uplift. Conclusion: reducing incentives often has outsized margin impact when incentives compose meaningful share; combine approaches for best effect while monitoring demand elasticity and retention.

An exact `COUNT(DISTINCT email) = COUNT(*)` check requires a full scan and a large in-memory or spill-to-disk distinct set, which doesn't hold up at billions of rows; approximate cardinality algorithms trade a small, bounded error for a check that runs in a fraction of the time and memory.

Approaches

• HyperLogLog / APPROX_COUNT_DISTINCT: gives an estimated distinct count with typically well under 2% error using a small fixed-size sketch, regardless of table size; compare it against the total row count to estimate a duplication rate without ever materializing the full distinct set.
• Bloom filters: build one incrementally as you scan; a bloom filter answers "have we seen this exact value before" with no false negatives (only a tunable false-positive rate), letting you flag CANDIDATE duplicates cheaply and confirm the smaller candidate set exactly.
• Partitioned verification: if the table is partitioned, verify uniqueness within each partition cheaply and only check across-partition uniqueness for keys that could plausibly repeat (for example if the partition key is unrelated to the uniqueness key).

Worked example

On a 5 billion row table, `APPROX_COUNT_DISTINCT(email)` typically returns an estimate within roughly 1-2% of the true distinct count while scanning the same data far faster than an exact `GROUP BY`, because it never needs to hold the full set of distinct values in memory.

Trade-offs and pitfalls

An approximate check can tell you "roughly how much duplication exists" but cannot point to which specific rows are the duplicates; if you need to actually remediate, you still need an exact pass, just possibly limited to the range the approximate check flagged as suspicious. The same underlying sketch techniques (HyperLogLog for cardinality, t-digest for quantiles) generalize well beyond uniqueness checks, into any large-scale distribution comparison where an exact answer is too expensive to compute routinely.

Direct answer
A push-delivery system for a recurring executive report needs to handle personalization per recipient, guarantee delivery even when something fails partway through, keep a clear record of what was actually sent, and give the report's owner a way to catch a mistake before dozens of executives see it.

Structured elaboration
Personalization: each recipient's report is generated from the same underlying template and data, but filtered or configured to their specific scope (a regional VP sees their region's numbers, not everyone's), which means the generation step needs to run once per recipient (or per recipient group sharing the same view), not just once globally.

Format and attachments: rendering to PDF or image for email delivery means the rendering step is a real part of the pipeline, not an afterthought, and needs its own failure handling (a rendering failure for one recipient's personalized view shouldn't block the other 29 recipients' reports from sending).

Retry on failure: if the send fails partway through (an email service hiccup, a rendering timeout for one recipient), the system needs to retry just the failed portions, not silently drop them or resend everyone's report and risk duplicates.

Audit trail: a record of exactly what was sent to whom and when, which matters both for troubleshooting ('did exec X actually receive Monday's report') and for compliance in some industries where a record of what information reached whom is a real requirement.

Preview before sending: the report owner should be able to see exactly what a given recipient's personalized report will look like before the scheduled send actually goes out, catching an obviously wrong number or a broken personalization filter before 30 executives see it, not after.

Worked example
A weekly executive KPI (key performance indicator) pack, personalized per recipient (each of 30 executives sees their own business unit's numbers plus a shared company-wide summary section), scheduled to send every Monday at 7 AM: the pipeline generates each recipient's personalized PDF the night before, runs a preview step where the report owner can spot-check a sample of the 30 personalized renders by Sunday evening, and if everything looks right, the actual send executes Monday morning. If the send to 3 of the 30 recipients fails (a transient email-service error), the system retries just those 3, logs which ones needed a retry and why, and confirms successful delivery to all 30 before marking the run complete; the audit log shows, for any given Monday, exactly which recipients received their report, when, and whether a retry was needed. If a data issue is caught during the Sunday-evening preview (one business unit's numbers look clearly wrong), the report owner can hold that recipient's send while the other 29 go out on schedule, rather than either delaying everyone or sending a known-wrong report to avoid missing the deadline.

Trade-offs and pitfalls
Personalization at scale (rendering 30, or 300, individually-customized reports) is meaningfully more expensive than generating one shared report, and the rendering step in particular (turning data into a polished PDF) can be slow enough that it needs to run well ahead of the actual send time, with real headroom built in for a retry, rather than being scheduled right up against the deadline. The preview step is also easy to skip under time pressure ('we're running late, just send it'), which is exactly when a real mistake is most likely to reach every recipient at once; treating the preview as a non-negotiable gate, not an optional nicety, is what actually prevents a bad report from reaching 30 executives simultaneously.

Top-level problem: Region revenue = -30% vs target. MECE issue tree split into Demand-side (commercial + product) and Supply-side (operational) and External factors.

1. Commercial levers — Demand & conversion

• Branches (MECE): Pricing & promotion; Sales coverage & channel mix; Marketing effectiveness
• Diagnostic metrics:
  • Price elasticity (revenue change per % price change) & promo ROI (incremental revenue / promo spend)
  • Sales coverage ratio (active reps / target accounts), win rate by channel
• Triage:
  • Quick wins: Pause ineffective promos, targeted price adjustments on high-elasticity SKUs, reallocate digital ad spend to top-performing segments (2–4 weeks, A/B tests)
  • Long term: Revise pricing strategy, optimize channel partnerships, hire/train reps, CRM segmentation (3–12 months)

2. Product levers — Local fit & assortment

• Branches: Localization (catalog, language, payment), SKU assortment & feature-market fit
• Diagnostic metrics:
  • Conversion rate by localized vs non-localized pages; cart abandonment by payment method
  • SKU-level revenue concentration and days-of-inventory
• Triage:
  • Quick wins: Enable local payment options, translate top product pages, remove 0-sales SKUs (1–6 weeks)
  • Long term: Local R&D/roadmap, tailored assortments, A/B test feature bundles (3–9 months)

3. Operational levers — Fulfillment & post-sale experience

• Branches: Fulfillment speed & costs; Returns & customer service
• Diagnostic metrics:
  • On-time delivery %, average delivery time, fulfillment cost per order
  • Return rate % and Net Promoter Score (NPS) by cohort
• Triage:
  • Quick wins: Prioritize faster shipping for high-margin SKUs, temporary local warehousing partners, expedite returns processing (2–8 weeks)
  • Long term: Build regional DC, renegotiate carrier SLAs, invest in automation and reverse logistics (6–18 months)

4. External factors — Market & regulation

• Branches: Macroeconomic conditions & currency; Competitive moves & distribution constraints; Regulatory/tariff changes
• Diagnostic metrics:
  • Market demand index (category sales trend), FX-adjusted revenue change
  • Share of voice/price gap vs competitors; days lost to customs/regulatory incidents
• Triage:
  • Quick wins: Hedge currency exposure short-term, competitive price/promotional response in targeted segments (1–4 weeks)
  • Long term: Lobbying/partnerships for regulatory barriers, diversify sourcing/markets (6–24 months)

Execution plan for BI Analyst:

• Immediate 2-week dashboard: daily revenue drilldowns by branch above, price elasticity cohort, delivery & return hotspots.
• 30-day deep-dive: SKU-level cohort analysis, channel unit economics, and experiment matrix for quick-win tests.
• Deliverables: Executive one-pager with prioritized quick wins (impact vs effort), A/B test specs, and KPIs to monitor during roll-out.

Compute each contract's constant daily rate (total_amount divided by the inclusive day count of its term), generate a month calendar spanning the reporting window, then for every contract-month pair compute the overlap in days between the contract's term and that calendar month. Recognized revenue for that contract in that month is daily_rate * overlap_days; summing across contracts per month gives the portfolio total, and the overlap calculation is exactly what prorates a contract whose start or end falls mid-month.

Approach

1. term_days per contract: inclusive day count from start_date to end_date (julianday(end) - julianday(start) + 1; add 1 because both endpoints belong to the term).
2. daily_rate = total_amount / term_days. This is the core straight-line assumption: revenue accrues evenly, one day at a time, for the life of the contract, not in even monthly chunks.
3. Generate month buckets across the reporting window (a recursive CTE here; a reusable calendar table in production).
4. Join contracts to months wherever the contract's term and the month's range overlap at all: start_date <= month_end AND end_date >= month_start.
5. overlap_days is the inclusive day count of the intersection of [contract start, contract end] and [month start, month end], using MAX() of the two starts and MIN() of the two ends (SQLite's scalar multi-argument MAX()/MIN(), confirmed here to propagate NULL: SELECT MAX(1, NULL, 3) returns NULL). BigQuery's GREATEST()/LEAST() match that NULL-propagating behavior, but Postgres's GREATEST()/LEAST() do not: per the Postgres documentation, they explicitly ignore NULL arguments and only return NULL if every argument is NULL, a documented deviation from the SQL standard. That is not a hypothetical edge case for this query: an open-ended contract with a NULL end_date would make MIN(end_date, month_end) collapse to NULL here (and on BigQuery), silently dropping that contract's row from every month, while the same pattern ported to Postgres's LEAST() would ignore the NULL and keep computing a result, a different, wrong-either-way outcome depending on engine. Guard with COALESCE(end_date, '9999-12-31') (or another far-future sentinel) before the MIN/LEAST call wherever this pattern needs to run identically across engines.
6. recognized_amount = daily_rate * overlap_days, summed per month for the portfolio total.

Worked example

CREATE TABLE contracts (
  contract_id TEXT PRIMARY KEY,
  start_date  TEXT,
  end_date    TEXT,
  total_amount NUMERIC
);

-- C1: mid-month start AND mid-month end (59-day term)
INSERT INTO contracts VALUES ('C1', '2026-01-15', '2026-03-14', 5900);
-- C2: clean calendar-month term (28-day term)
INSERT INTO contracts VALUES ('C2', '2026-02-01', '2026-02-28', 2800);
-- C3: mid-month to mid-month, spans into the next month
INSERT INTO contracts VALUES ('C3', '2026-03-10', '2026-04-09', 3100);

WITH RECURSIVE months(month_start) AS (
  SELECT date('2026-01-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2026-04-01')
),
-- months builds in passes: the base row is 2026-01-01, then each pass takes
-- the previous pass's row and builds the next month from it (date(month_start,
-- '+1 month')), re-running against months's own growing output, until a
-- pass's output fails the WHERE condition and the recursion stops.
month_ranges AS (
  SELECT month_start, date(month_start, '+1 month', '-1 day') AS month_end
  FROM months
),
term AS (
  SELECT
    contract_id, start_date, end_date, total_amount,
    julianday(end_date) - julianday(start_date) + 1 AS term_days
  FROM contracts
),
alloc AS (
  SELECT
    t.contract_id,
    mr.month_start,
    MAX(t.start_date, mr.month_start)                                 AS overlap_start,
    MIN(t.end_date,   mr.month_end)                                   AS overlap_end,
    julianday(MIN(t.end_date, mr.month_end))
      - julianday(MAX(t.start_date, mr.month_start)) + 1               AS overlap_days,
    t.total_amount, t.term_days
  FROM term t
  JOIN month_ranges mr
    ON t.start_date <= mr.month_end AND t.end_date >= mr.month_start
)
SELECT
  strftime('%Y-%m', month_start)                              AS month,
  contract_id, overlap_start, overlap_end, overlap_days,
  ROUND(total_amount / term_days, 4)                          AS daily_rate,
  ROUND((total_amount / term_days) * overlap_days, 2)         AS recognized_amount
FROM alloc
WHERE overlap_days > 0
ORDER BY month, contract_id;

-- Portfolio total recognized revenue per month
-- (CTE scope is per statement: repeat the WITH clause when running this standalone)
WITH RECURSIVE months(month_start) AS (
  SELECT date('2026-01-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2026-04-01')
),
-- months builds in passes: the base row is 2026-01-01, then each pass takes
-- the previous pass's row and builds the next month from it (date(month_start,
-- '+1 month')), re-running against months's own growing output, until a
-- pass's output fails the WHERE condition and the recursion stops.
month_ranges AS (
  SELECT month_start, date(month_start, '+1 month', '-1 day') AS month_end
  FROM months
),
term AS (
  SELECT
    contract_id, start_date, end_date, total_amount,
    julianday(end_date) - julianday(start_date) + 1 AS term_days
  FROM contracts
),
alloc AS (
  SELECT
    t.contract_id,
    mr.month_start,
    MAX(t.start_date, mr.month_start)                                 AS overlap_start,
    MIN(t.end_date,   mr.month_end)                                   AS overlap_end,
    julianday(MIN(t.end_date, mr.month_end))
      - julianday(MAX(t.start_date, mr.month_start)) + 1               AS overlap_days,
    t.total_amount, t.term_days
  FROM term t
  JOIN month_ranges mr
    ON t.start_date <= mr.month_end AND t.end_date >= mr.month_start
)
SELECT
  strftime('%Y-%m', month_start)                                    AS month,
  ROUND(SUM((total_amount / term_days) * overlap_days), 2)          AS recognized_revenue
FROM alloc
WHERE overlap_days > 0
GROUP BY month
ORDER BY month;

Result:

┌─────────┬─────────────┬───────────────┬─────────────┬──────────────┬────────────┬────────────────────┐
│  month  │ contract_id │ overlap_start │ overlap_end │ overlap_days │ daily_rate │ recognized_amount  │
├─────────┼─────────────┼───────────────┼─────────────┼──────────────┼────────────┼────────────────────┤
│ 2026-01 │ C1          │ 2026-01-15    │ 2026-01-31  │ 17.0         │ 100.0      │ 1700.0             │
│ 2026-02 │ C1          │ 2026-02-01    │ 2026-02-28  │ 28.0         │ 100.0      │ 2800.0             │
│ 2026-02 │ C2          │ 2026-02-01    │ 2026-02-28  │ 28.0         │ 100.0      │ 2800.0             │
│ 2026-03 │ C1          │ 2026-03-01    │ 2026-03-14  │ 14.0         │ 100.0      │ 1400.0             │
│ 2026-03 │ C3          │ 2026-03-10    │ 2026-03-31  │ 22.0         │ 100.0      │ 2200.0             │
│ 2026-04 │ C3          │ 2026-04-01    │ 2026-04-09  │ 9.0          │ 100.0      │ 900.0              │
└─────────┴─────────────┴───────────────┴─────────────┴──────────────┴────────────┴────────────────────┘

-- Portfolio total recognized revenue per month --
┌─────────┬────────────────────┐
│  month  │ recognized_revenue │
├─────────┼────────────────────┤
│ 2026-01 │ 1700.0             │
│ 2026-02 │ 5600.0             │
│ 2026-03 │ 3600.0             │
│ 2026-04 │ 900.0              │
└─────────┴────────────────────┘

Contract C1 runs January 15 through March 14, a 59-day, $5,900 term, exactly $100/day. January gets 17 days ($1,700, Jan 15-31), February gets the full 28 days ($2,800), March gets 14 days ($1,400, March 1-14). Those three pieces sum back to exactly $5,900, confirming no revenue was lost or double-counted at the boundaries. C3 (March 10 to April 9, $3,100, also $100/day) similarly splits into $2,200 in March and $900 in April. Portfolio totals per month: January $1,700; February $5,600 (C1 + C2); March $3,600 (C1's tail plus C3's start); April $900 (C3's tail).

The same technique, reframed

This overlap-day allocation is a general tool, not a one-off trick for "recognized revenue per month." The identical technique answers several sibling questions you may be asked instead:

• Deferred revenue balance: instead of summing recognized days, sum the unrecognized days remaining on each contract as of a snapshot date (term_days minus days elapsed so far), times daily_rate. This gives the balance-sheet liability at that point in time, not the income-statement flow.
• Forward-looking forecast: run the exact same monthly bucketing, but for months that haven't happened yet, to project how much of each currently-signed contract's revenue will recognize in future periods.
• ACV and backlog bucketing: the same straight-line allocation idea applied to annual contract value (ACV) or to backlog, spreading the total contracted value into monthly (or quarterly) buckets the same overlap-based way, rather than into GAAP-recognized revenue specifically.

Trade-offs & pitfalls

• Complexity: the month-generation CTE is O(months in range); the contract-month join is O(contracts x overlapping months), cheap for a normal-sized contract book, but a contract with a multi-year term joins against many month rows, worth watching if contracts can run 5+ years.
• Edge cases: a contract that starts and ends within the same month (overlap_days equals the full term_days; the join condition start <= month_end AND end >= month_start still catches it correctly); a 1-day contract term (term_days = 1, daily_rate = total_amount, no special case needed, the formula still works); contract amendments that change total_amount or dates mid-term retroactively (this model assumes the current row is authoritative for the whole historical term; a real system needs an effective-dated amendments table, not just an in-place UPDATE).
• Common wrong turn: prorating by months instead of by days, that is, dividing total_amount evenly across however many calendar months the contract touches, ignoring that touching a month for 3 days versus 28 days shouldn't recognize the same amount. This is the single most common shortcut that produces materially wrong numbers for any contract with a mid-month boundary.
• Reconciliation: this recognized-revenue schedule should tie out to the deferred revenue balance on the general ledger (unrecognized-to-date should always equal total contract value minus recognized-to-date). If it doesn't reconcile, look first for cents-level rounding applied per row rather than aggregating everything before rounding once.