SQL for Data Analysis Questions

Direct answer

Run a small set of targeted diagnostics rather than staring at the one aggregate gap number: win rate and forecast inclusion by pipeline stage, close-date slippage for deals that did close, and whether discounting is eroding realized value. Then isolate which effect actually explains the gap by decomposing "realized revenue" into revenue from deals that WERE in the forecast versus revenue from deals that were never in the forecast snapshot at all, since those are different root causes needing different fixes.

Structured elaboration

• Win rate and forecast inclusion by stage: a forecast typically only counts deals above some stage threshold (say, Committed and Qualified) as "in the number", with earlier-stage deals excluded as too speculative. If win rate for an included stage is higher than the forecast model assumes, or if a meaningful share of realized revenue comes from deals that were excluded entirely, that is a structural underestimate, not noise.
• Close-date slippage: measure actual_close_date - initial_expected_close in days for deals that did close. Slippage is a timing problem (revenue lands in a later period than forecast said), which explains period-level misses even when the eventual total is fine; it does not by itself explain an aggregate underestimate.
• Discounting / deal-value drift: compare actual_amount to forecast_amount for closed deals. This is usually framed as discount erosion (actual below forecast), but the same query answers the opposite question just as well: if actual consistently exceeds forecast (upsell, expansion, under-discounting), that shows up as a realization ratio above 1.0.
• Decomposition, not just one number: split realized revenue into "from deals the forecast knew about" and "from deals the forecast never included", these are genuinely different problems (a calibration problem inside the forecast model vs a pipeline-coverage/visibility gap outside it) and need different fixes.

Worked example (executed, sqlite3)

CREATE TABLE opportunities (
  opp_id TEXT, sales_rep TEXT, stage TEXT, is_won INTEGER,
  forecast_amount NUMERIC,  -- NULL means NOT included in the forecast snapshot
  actual_amount NUMERIC, initial_expected_close TEXT, actual_close_date TEXT, discount_pct NUMERIC
);
INSERT INTO opportunities VALUES
  ('OPP1','RepA','Committed',1,10000,10000,'2025-01-15','2025-01-15',0.00),
  ('OPP2','RepA','Qualified',1,8000, 9200, '2025-01-20','2025-02-10',0.00),
  ('OPP3','RepB','Early',    0,NULL, 0,    '2025-01-25','2025-01-25',0.00),
  ('OPP4','RepB','Committed',1,12000,12000,'2025-02-01','2025-02-01',0.00),
  ('OPP5','RepC','Qualified',1,6000, 6900, '2025-02-05','2025-03-01',0.00),
  ('OPP6','RepC','Early',    1,NULL, 6250, '2025-02-08','2025-02-08',0.00),  -- won, never forecasted
  ('OPP7','RepA','Qualified',1,7000, 8050, '2025-02-15','2025-03-20',0.00),
  ('OPP8','RepB','Committed',1,4000, 4000, '2025-02-20','2025-02-20',0.00);

Win rate and forecast total by stage:

SELECT stage, COUNT(*) opp_count, SUM(is_won) wins, ROUND(1.0*SUM(is_won)/COUNT(*),2) win_rate,
       SUM(forecast_amount) total_forecast,
       SUM(CASE WHEN is_won=1 THEN actual_amount ELSE 0 END) total_actual_won
FROM opportunities GROUP BY stage;

Close-date slippage, forecast-tracked won deals only:

SELECT opp_id, stage, CAST(julianday(actual_close_date) - julianday(initial_expected_close) AS INTEGER) AS days_slipped
FROM opportunities WHERE is_won=1 AND forecast_amount IS NOT NULL ORDER BY days_slipped DESC;

Result: OPP7 slipped 33 days, OPP5 slipped 24 days, OPP2 slipped 21 days; OPP1, OPP4, OPP8 slipped 0 days.

Discounting, forecast-tracked won deals only:

SELECT ROUND(AVG(discount_pct),3) avg_discount, SUM(forecast_amount) total_forecast, SUM(actual_amount) total_actual
FROM opportunities WHERE is_won=1 AND forecast_amount IS NOT NULL;

Result: avg_discount = 0.0, total_forecast = 47000, total_actual = 50150. Discounting is ruled out as a driver here (it is exactly zero), and realized revenue on forecast-tracked deals already runs above forecast (50150 vs 47000).

Root-cause decomposition:

SELECT
  SUM(CASE WHEN is_won=1 AND forecast_amount IS NOT NULL THEN actual_amount ELSE 0 END) AS actual_from_forecasted_deals,
  SUM(CASE WHEN is_won=1 AND forecast_amount IS NULL THEN actual_amount ELSE 0 END) AS actual_from_unforecasted_deals
FROM opportunities;

Result: actual_from_forecasted_deals = 50150, actual_from_unforecasted_deals = 6250.

SELECT (SELECT SUM(forecast_amount) FROM opportunities) AS total_forecast,
       (SELECT SUM(actual_amount) FROM opportunities WHERE is_won=1) AS total_realized,
       ROUND((1.0*(SELECT SUM(actual_amount) FROM opportunities WHERE is_won=1)
              / (SELECT SUM(forecast_amount) FROM opportunities) - 1) * 100, 1) AS pct_forecast_error;

Result: total_forecast = 47000, total_realized = 56400, pct_forecast_error = 20.0.

On this seed data, two effects together explain the full 20% gap: Qualified-stage deals realized $24150 against a forecast of $21000 (deal values ran above what was forecast for them, discounting ruled out as the cause), and $6250 of realized revenue (11.1% of the total realized) came from Early-stage deals that were never in the forecast snapshot at all.

Trade-offs and pitfalls

• Slippage and magnitude are different axes and get conflated easily: a deal that slips 33 days but still closes at its forecast value is a period-attribution problem, not an underestimation problem; do not let a slippage finding get reported as "the reason the total was wrong."
• Proposed tracking metrics: (1) monthly forecast error, (realized - forecast) / forecast computed per cohort month of initial_expected_close, tracked over time to see whether the gap is shrinking; (2) share of realized revenue coming from deals that were never in the forecast snapshot, since that number isolates the pipeline-coverage problem from the deal-value-calibration problem and the two need different fixes (better stage-inclusion rules and win-rate weighting vs earlier deal visibility/qualification discipline).
• A single blended "20% off" headline number invites the wrong fix (commonly, "apply a 20% haircut to all forecasts") when the real drivers are stage-specific and partly about visibility, not calibration; the decomposition above is what prevents that wrong turn.
• These diagnostics depend on a forecast snapshot being captured at forecast time (not reconstructed after the fact from current data), otherwise "was this deal in the forecast" cannot be answered accurately for historical periods.

My build approach

1. Convert the transaction range into an Excel Table so the source expands automatically.
2. Insert a PivotTable on a separate summary sheet.
3. Put Date in Rows and group it by Months and Years.
4. Add Region, Product, and Channel as filters or slicers depending on how much detail the audience needs.
5. Put the measure, such as Sales Amount or Units, in Values and format it as currency or number.

For trend lines

• I would create a PivotChart from the PivotTable, usually a line chart for monthly trends.
• If the audience wants comparison across regions, I would use region as a legend series or create separate small charts.

Making it usable for non-technical stakeholders

• Add slicers for Region, Product, and Channel.
• Add a timeline for date filtering if the source is a real date field.
• Hide field buttons and use friendly labels like "Monthly Revenue" instead of technical field names.
• Use a clean layout with one summary tab and one detail tab.
• Keep number formatting consistent and avoid showing raw pivot clutter.

Performance and usability

• Refresh from the Table, not a copied range.
• Keep calculations simple inside the pivot.
• If the workbook is large, I would avoid lots of extra formulas on top of the PivotTable and let the pivot do the aggregation.

Join each transaction to fx_rates on matching currency and rate_date <= transaction_date, then use ROW_NUMBER() partitioned by transaction and ordered by rate_date DESC to pick the single most recent rate at or before the transaction date. That's a last-observation-carried-forward join: instead of requiring an exact-date match, you rank all eligible prior rates and keep the top one.

The join pattern

A naive fx_rates.rate_date = transactions.transaction_date join fails the moment a rate is missing for a given day (a weekend, a holiday, a data gap), which drops that transaction from the result entirely. The fix is a range join (rate_date <= transaction_date) followed by a window function to collapse the resulting one-to-many matches down to the single best candidate per transaction:

WITH ranked AS (
  SELECT
    t.transaction_id,
    t.transaction_date,
    t.currency,
    t.amount,
    r.rate_date,
    r.usd_per_unit,
    ROW_NUMBER() OVER (
      PARTITION BY t.transaction_id
      ORDER BY r.rate_date DESC
    ) AS rn
  FROM transactions t
  LEFT JOIN fx_rates r
    ON r.currency_code = t.currency
    AND r.rate_date <= t.transaction_date
)
SELECT transaction_id, transaction_date, currency, amount,
       rate_date AS rate_used_from, usd_per_unit,
       ROUND(amount * usd_per_unit, 2) AS usd_amount
FROM ranked
WHERE rn = 1
ORDER BY transaction_id;

LEFT JOIN (not INNER JOIN) matters here too: it keeps a transaction in the output (with a NULL rate) if no prior rate exists at all yet, rather than dropping it silently, the same silent-drop risk as any other join.

Worked example

Run in sqlite3 with dates stored as ISO-8601 text (sqlite3 has no native DATE type, so string comparison and lexical ordering both happen to work). In Postgres, MySQL, SQL Server, Snowflake, or BigQuery, rate_date and transaction_date would be native DATE columns; the join condition and ROW_NUMBER() logic are unchanged, only the column typing differs.

Seed fx_rates(rate_date, currency_code, usd_per_unit) with EUR rates on 2026-07-01 and 2026-07-03 (no rate on 2026-07-02, a deliberate gap) and a GBP rate on 2026-07-01:

fx_rates: (2026-07-01, EUR, 1.08), (2026-07-03, EUR, 1.09), (2026-07-01, GBP, 1.27)

Seed 4 transactions, including one that lands exactly on the gap day:

transactions: (1, 2026-07-01, EUR, 100), (2, 2026-07-02, EUR, 200),
              (3, 2026-07-03, EUR, 50),  (4, 2026-07-01, GBP, 100)

Run output:

transaction_id  transaction_date  currency  amount  rate_used_from  usd_per_unit  usd_amount
1               2026-07-01        EUR       100     2026-07-01      1.08          108.0
2               2026-07-02        EUR       200     2026-07-01      1.08          216.0
3               2026-07-03        EUR       50      2026-07-03      1.09          54.5
4               2026-07-01        GBP       100     2026-07-01      1.27          127.0

Transaction 2, dated 2026-07-02 with no EUR rate that day, correctly falls back to the 2026-07-01 rate (1.08) rather than being dropped or getting a NULL rate. Transaction 3, dated exactly on 2026-07-03, uses that day's own rate (1.09) rather than the older 1.08, confirming the ORDER BY rate_date DESC picks the closest prior rate, not just any prior rate.

Trade-offs and pitfalls

• Correlated-subquery alternative: instead of ROW_NUMBER(), you can use a correlated subquery (SELECT usd_per_unit FROM fx_rates WHERE currency_code = t.currency AND rate_date <= t.transaction_date ORDER BY rate_date DESC LIMIT 1) or a lateral join where the engine supports it. The window-function version above tends to be easier for the optimizer to turn into a single merge-join-like plan across many transactions at once, versus paying a per-row subquery cost.
• Indexing: fx_rates(currency_code, rate_date) as a composite index is what makes the range join cheap; without it, every transaction potentially scans the whole rate history for its currency.
• Missing rate entirely: if a currency has no rate on or before the transaction date at all (e.g. a brand-new currency added to transactions before fx_rates was backfilled), the LEFT JOIN leaves usd_per_unit NULL and usd_amount NULL rather than erroring; decide deliberately whether to flag those for manual review or fall back to a default, don't let them silently zero out in a downstream SUM.
• Staleness risk: this pattern can carry a rate forward indefinitely if there's a long gap (a currency stops being reported for months); consider capping how far back the fallback is allowed to reach, or flagging transactions whose matched rate is more than N days stale.
• Retroactive rate corrections: if fx_rates is later corrected for a historical date, any transaction converted using the old value needs to be reprocessed; this join is not idempotent against a fx_rates update unless you re-run it.

Direct answer

Treat a sudden conversion-rate drop as an incident, not a metrics question. Before entertaining a real business explanation, rule out the two causes that are far more common and far cheaper to check: an instrumentation break (an event stopped firing somewhere) and a data-pipeline failure (a load didn't run, a join broke). The triage order matters: volume before rate, segment before aggregate, and correlate with recent changes only after data plumbing is confirmed clean.

Structured elaboration

Work through four tiers in order. Each tier is designed to be cheap and fast, so you only pay for expensive investigation once cheaper checks have narrowed the field.

The key discipline: don't jump to tier 4 first. A "the checkout flow is broken" story is often actually a tier-2 story (an event stopped firing) wearing a tier-4 costume, and you can tell them apart in minutes by checking whether raw event volume moved or only the computed rate moved.

Worked example

Seed data: a raw_events fact table with page_view and purchase event counts per day and channel (ios, android, web), for March 1 to March 8, 2026. Baseline conversion rate is 6.0% every day and every channel. Starting March 7, the iOS purchase event count collapses while iOS page views stay flat, and android/web are untouched.

Query A: overall daily conversion rate (tier 1)

SELECT
  event_date,
  SUM(CASE WHEN event_name = 'purchase' THEN event_count ELSE 0 END) AS conversions,
  SUM(CASE WHEN event_name = 'page_view' THEN event_count ELSE 0 END) AS visits,
  ROUND(100.0 * SUM(CASE WHEN event_name = 'purchase' THEN event_count ELSE 0 END)
    / NULLIF(SUM(CASE WHEN event_name = 'page_view' THEN event_count ELSE 0 END), 0), 2) AS conv_rate_pct
FROM raw_events
GROUP BY event_date
ORDER BY event_date;

Result (run in DuckDB):

event_date | conversions | visits | conv_rate_pct
-----------+-------------+--------+--------------
2026-03-01 |          72 |   1200 |           6.0
2026-03-02 |          72 |   1200 |           6.0
2026-03-03 |          72 |   1200 |           6.0
2026-03-04 |          72 |   1200 |           6.0
2026-03-05 |          72 |   1200 |           6.0
2026-03-06 |          72 |   1200 |           6.0
2026-03-07 |          44 |   1200 |          3.67
2026-03-08 |          44 |   1200 |          3.67

Total daily visits never moves (1200 every day). That single fact already rules out a broad tier-2 traffic/pipeline outage: if the pipeline were failing wholesale, page views would have dropped too. The drop is concentrated entirely in the numerator.

Query C: break the drop window down by channel (tier 1/2, localize further)

SELECT
  channel,
  SUM(CASE WHEN event_name='purchase' THEN event_count ELSE 0 END) AS conversions,
  SUM(CASE WHEN event_name='page_view' THEN event_count ELSE 0 END) AS visits,
  ROUND(100.0*SUM(CASE WHEN event_name='purchase' THEN event_count ELSE 0 END)
    / NULLIF(SUM(CASE WHEN event_name='page_view' THEN event_count ELSE 0 END),0),2) AS conv_rate_pct
FROM raw_events
WHERE event_date >= DATE '2026-03-07'
GROUP BY channel
ORDER BY conv_rate_pct;

Result:

channel | conversions | visits | conv_rate_pct
--------+-------------+--------+--------------
ios     |           4 |   1000 |           0.4
web     |          36 |    600 |           6.0
android |          48 |    800 |           6.0

iOS visits are still 1000 for the two days (500/day, unchanged), but iOS purchases collapsed to 4 total. Android and web are exactly at baseline. That pattern (traffic flat, one channel's conversion event specifically gone) is the signature of an instrumentation break on that channel's purchase event, not a real behavioral change (a genuine product regression on iOS would usually still show some signal in upstream funnel steps or a slower decay, not a same-day cliff isolated to one event type).

Query D: correlate with recent releases (tier 4, confirmation, not the starting point)

SELECT * FROM deploys WHERE deploy_date >= DATE '2026-03-05' ORDER BY deploy_date;

Result:

deploy_date | service | description
------------+---------+--------------------------------------
2026-03-07  | ios-app | v4.2.0 release: checkout flow rewrite

An iOS release landed the same day the purchase event vanished. Combined with flat visits and unaffected sibling channels, the most defensible conclusion is: the iOS v4.2.0 checkout rewrite broke the purchase-event fire client-side. This is a tier-2/tier-3 story (instrumentation), confirmed rather than assumed, in under four queries.

Trade-offs & pitfalls

• Resist the urge to "fix the query" the moment a number looks wrong. If Query A's rate had dropped but Query C showed every channel down equally, that would point toward a real query or pipeline bug shared across channels, not an instrumentation break, a very different fix.
• Don't over-trust a deploy-date correlation on its own. It is confirming evidence here because volume and segment checks already isolated the same channel; used as the first signal, it would be a coincidence trap (releases happen constantly, and something is always deploying near any given day).
• Annotate the dashboard as provisional the moment you suspect a data issue, before you've fully root-caused it. Stakeholders making decisions off a broken number for even a day is a worse outcome than a slightly slower diagnosis.
• Once root-caused, decide explicitly whether to backfill (if the raw purchase events can be recovered from a different source, e.g. payment-processor webhooks) or to exclude and annotate the affected window, don't silently interpolate a "fixed" number.

Direct answer

GROUP BY collapses every row in a group down into one summary row (a count, a sum, an average). A window function computes that same kind of aggregate or ranking, but keeps one output row per input row, because the calculation runs "over a window" of related rows without collapsing them. Reach for a window function whenever the result needs to sit alongside the original row-level detail, such as a running total, a cumulative count, or a rank per row, rather than replacing it.

Structured elaboration

The signal to look for in the question: does the expected output have "one row per X" (X = user, order, day) where X still needs to appear, but also needs an aggregate or rank attached? If yes, that is a window function, not GROUP BY, even if an aggregate function like SUM or COUNT is involved.

Worked example

Seed data: 10 signups across 4 weekly cohorts.

CREATE TABLE signups (user_id INTEGER, cohort_week DATE);
-- 3 users in week of 2026-06-01, 2 in 2026-06-08, 4 in 2026-06-15, 1 in 2026-06-22

GROUP BY collapses the rows you need (one row per user is gone):

SELECT cohort_week, COUNT(user_id) AS new_users
FROM signups GROUP BY cohort_week ORDER BY cohort_week;

Real output:

A window function is the right tool when the stakeholder also wants the running cumulative total next to each week (still one row per week, group-level, but now with a value that depends on prior groups too):

SELECT cohort_week, COUNT(user_id) AS weekly_new,
       SUM(COUNT(user_id)) OVER (ORDER BY cohort_week) AS cumulative_new
FROM signups GROUP BY cohort_week ORDER BY cohort_week;

Real output:

And when you need one row per user (not per week) with a rank attached, GROUP BY cannot produce that at all, because GROUP BY would collapse the users away. A window function with no GROUP BY does it directly:

SELECT user_id, cohort_week,
       RANK() OVER (PARTITION BY cohort_week ORDER BY user_id) AS signup_rank_in_week
FROM signups ORDER BY cohort_week, user_id;

Real output (first 4 of 10 rows; every user_id is preserved):

Trade-offs & pitfalls

• The most common mistake is reaching for a self-join or a correlated subquery to get a running total or rank, when a window function does the same thing in one pass and is both simpler to read and generally faster.
• Window functions do not reduce row count, so on very large tables (hundreds of millions of rows) they can be more expensive than a GROUP BY that collapses the data first; if the frame is unbounded (like ORDER BY cohort_week with no explicit bound, which defaults to "everything up to the current row"), the engine has to look back arbitrarily far for every row.
• GROUP BY and window functions are not mutually exclusive: the cumulative-total example above uses both together, aggregating with GROUP BY first and then running a window function over the grouped result.