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.

Approach

I would automate the steps that are repetitive and easy to validate first: import the CSV, clean the headers, normalize the data types, refresh summary sheets, then hand off PDF export to Power Automate or a similar wrapper if I am using Office Scripts. That is because Office Scripts is great for workbook logic, but PDF distribution is usually better handled outside the script.

Robustness

• Check that the expected columns exist before doing anything else.
• Trim header names, remove extra spaces, and standardize case.
• Reject or log rows with missing required fields.
• Parse dates carefully and flag values that do not convert.
• Write problems to an error sheet instead of failing silently.
• Refresh pivots or formulas only after the raw data passes basic validation.

Sample Office Script pattern

function main(workbook: ExcelScript.Workbook) {
  const raw = workbook.getWorksheet('Raw');
  const errors = workbook.getWorksheet('Errors') || workbook.addWorksheet('Errors');

  // Read used range and validate headers
  const range = raw.getUsedRange();
  if (!range) throw new Error('Raw sheet is empty');

  const values = range.getValues();
  const headers = values[0].map(h => String(h).trim().replace(/\s+/g, ' '));

  // Example: ensure required columns exist
  const required = ['Date', 'Amount', 'Region'];
  required.forEach(col => {
    if (!headers.includes(col)) throw new Error('Missing column: ' + col);
  });

  // Clean summary sheets after validation
  workbook.refreshAllDataConnections();
  workbook.getWorksheets().forEach(ws => {
    if (ws.getName().startsWith('Summary')) {
      ws.getUsedRange()?.getFormat().autofitColumns();
    }
  });
}

That structure makes the automation easier to debug and safer when the source CSV is messy.

Direct answer

Finding customers with zero orders is an anti-join: return every row from customers that has no corresponding row in orders. The two standard ANSI patterns are LEFT JOIN ... WHERE right_key IS NULL and NOT EXISTS (subquery); both are correct, and for this exact question they return identical results.

Approach

1. Start from customers (the side you need every row from) and LEFT JOIN to orders on the customer key.
2. A customer with no orders produces a joined row where every orders column is NULL.
3. Filter to exactly those rows with WHERE o.order_id IS NULL (an anti-join), or equivalently use NOT EXISTS to ask the same question without materializing a join.

Worked example (sqlite3, verified)

Sample data:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO customers VALUES
  (1, 'alice@x.com'), (2, 'bob@x.com'), (3, 'carol@x.com'), (4, 'dave@x.com');

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT);
INSERT INTO orders VALUES (101, 1, '2024-01-05'), (102, 1, '2024-03-10'), (103, 2, '2024-02-01');
-- customers 3 and 4 have zero orders

LEFT JOIN ... IS NULL:

SELECT c.customer_id, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Result: (3, 'carol@x.com'), (4, 'dave@x.com'). Exactly the 2 customers with no matching order row.

NOT EXISTS (equivalent):

SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;

Result: identical, (3, 'carol@x.com'), (4, 'dave@x.com').

Extension: also return the most recent order date, NULL if none. This is the natural follow-on to the same LEFT JOIN, just without filtering out matched rows, aggregated with MAX:

SELECT c.customer_id, c.email, MAX(o.order_date) AS most_recent_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.email
ORDER BY c.customer_id;

Result: (1, alice, '2024-03-10'), (2, bob, '2024-02-01'), (3, carol, NULL), (4, dave, NULL). MAX over an all-NULL group (Carol, Dave) correctly returns NULL rather than erroring or defaulting to 0.

Key points

• LEFT JOIN ... IS NULL and NOT EXISTS are semantically equivalent for a simple single-column anti-join like this one.
• The IS NULL filter must reference a column from the right table that is guaranteed non-NULL when a match exists (typically its primary key, o.order_id here), not an arbitrary right-side column that could itself legitimately be NULL in a matched row.
• The extension query keeps every customer (LEFT JOIN, no WHERE filter) and lets MAX(order_date) do double duty: it answers "most recent order" for customers with orders and naturally yields NULL for customers without any.

Complexity, edge cases & pitfalls

• Both patterns need an index on orders.customer_id to avoid a full scan per customer; with that index, NOT EXISTS typically compiles to a semi-join/anti-join plan that stops at the first match, which is often at least as fast as the LEFT JOIN plan and easier to reason about when more join conditions are added later.
• If orders.customer_id can itself be NULL (an order somehow unassigned), NOT EXISTS is unaffected, but a naive WHERE customer_id NOT IN (SELECT customer_id FROM orders) breaks completely: a single NULL in the subquery's result makes NOT IN return zero rows for the whole outer query. This is why NOT EXISTS (or the LEFT JOIN pattern) is preferred over NOT IN for anti-joins.
• If customers has duplicate customer_id values (a data quality issue, not expected under a primary key), the LEFT JOIN version could return the same customer more than once when it has orders, though the anti-join branch itself is unaffected since it only fires for customers with zero matches.

Direct answer

Assign each user to a signup week (a fixed Monday-start bucket derived from signup_date), do the same week-bucketing to their events, then for each cohort week report the percentage of that cohort with at least one event in cohort-week-plus-N, for each N you care about. The two things that make this correct rather than approximately correct: bucketing both signup and event dates with the exact same week-start rule, and computing week offset as a count of week boundaries crossed, not a raw day difference divided by 7.

Structured elaboration

1. Cohort assignment: truncate each user's signup_date to the Monday that starts its week.
2. Event bucketing: apply the identical week-truncation rule to every event's date, so "week 1" for a cohort always means the same calendar week regardless of which day inside it the signup or event happened to land on.
3. Offset computation: week_offset = (event_week - signup_week) / 7 days, an integer.
4. Percent retained: for each cohort week and offset, COUNT(DISTINCT users active at that offset) / cohort_size.

Worked example (executed, sqlite3), including a real bug caught while verifying this answer

Seed data: cohort A signs up the week of 2025-06-02 (a Monday, 4 users), cohort B the week of 2025-06-09 (3 users), with deliberately uneven activity across the following two weeks.

CREATE TABLE users (user_id INTEGER PRIMARY KEY, signup_date TEXT);
INSERT INTO users VALUES
  (1,'2025-06-02'), (2,'2025-06-03'), (3,'2025-06-05'), (4,'2025-06-07'),
  (5,'2025-06-09'), (6,'2025-06-10'), (7,'2025-06-13');

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events VALUES
  (1,'2025-06-02'), (1,'2025-06-10'), (1,'2025-06-17'),
  (2,'2025-06-03'), (2,'2025-06-11'),
  (3,'2025-06-05'),
  (4,'2025-06-07'), (4,'2025-06-20'),
  (5,'2025-06-09'), (5,'2025-06-18'),
  (6,'2025-06-10'),
  (7,'2025-06-13'), (7,'2025-06-19');

First attempt at week-truncation used SQLite's built-in weekday N date modifier:

-- Looked correct, but had a real trap: 'weekday 1' only ADVANCES to the next
-- Monday; if the input date IS ALREADY Monday, it leaves it unchanged, so
-- stepping back '-7 days' afterward overshoots by a full extra week:
SELECT strftime('%w','2025-06-02') AS dow,                        -- 1 (Monday)
       date('2025-06-02','weekday 1')            AS naive_step1,   -- 2025-06-02 (unchanged)
       date('2025-06-02','weekday 1','-7 days')  AS naive_result;  -- 2025-05-26 (WRONG, a week early)

Running the full retention query with that formula put user 1 (who signed up on a Monday) into the wrong cohort week entirely, one week ahead of the other three users who signed up later that same week. That is caught here specifically because the seed data includes a signup that lands exactly on the week boundary, the case most naive week-truncation logic gets wrong.

Corrected week-start formula, using day-of-week arithmetic instead of the modifier:

-- strftime('%w', d) is 0=Sunday..6=Saturday; (%w + 6) % 7 = days since Monday
date(d, '-' || ((strftime('%w', d) + 6) % 7) || ' days')

Full corrected query:

WITH cohorts AS (
  SELECT user_id, date(signup_date, '-' || ((strftime('%w', signup_date) + 6) % 7) || ' days') AS signup_week
  FROM users
),
cohort_sizes AS (SELECT signup_week, COUNT(*) AS cohort_size FROM cohorts GROUP BY signup_week),
event_weeks AS (
  SELECT user_id, date(event_date, '-' || ((strftime('%w', event_date) + 6) % 7) || ' days') AS event_week
  FROM events
),
activity AS (
  SELECT c.signup_week, c.user_id,
    CAST((julianday(e.event_week) - julianday(c.signup_week)) / 7 AS INTEGER) AS week_offset
  FROM cohorts c JOIN event_weeks e ON e.user_id = c.user_id
  WHERE e.event_week >= c.signup_week
)
SELECT cs.signup_week, cs.cohort_size,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=0 THEN a.user_id END)/cs.cohort_size,1) AS pct_week0,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=1 THEN a.user_id END)/cs.cohort_size,1) AS pct_week1,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=2 THEN a.user_id END)/cs.cohort_size,1) AS pct_week2
FROM cohort_sizes cs LEFT JOIN activity a ON a.signup_week = cs.signup_week
GROUP BY cs.signup_week, cs.cohort_size ORDER BY cs.signup_week;

Result:

Every user has an event in their own signup week by construction, so week 0 is correctly 100.0% for both cohorts once the truncation bug is fixed. Cohort A: users 1 and 2 return in week 1 (2/4 = 50.0%); users 1 and 4 return in week 2 (2/4 = 50.0%). Cohort B: users 5 and 7 return in week 1 (2/3 = 66.7%); nobody returns in week 2 (0.0%), matching the seeded data exactly.

Trade-offs and pitfalls

• The week-truncation trap above is a genuine date-boundary off-by-one, the same failure family as a date_trunc cohort bug: it is invisible for signups in the middle of a week and only surfaces for a signup that lands exactly on the week-start day, so a test suite needs at least one such boundary case to catch it.
• Extending to day 1 through day 30: the same activity CTE pattern generalizes by computing offset in days instead of weeks (julianday(event_date) - julianday(signup_date)), but doing this efficiently for many offsets at once favors a single pass that buckets every event into its offset once, rather than a separate correlated subquery per offset day.
• Timezone-adjusted cohorts: if signup_date/event_date are UTC timestamps and users span timezones, the week boundary itself should be computed in each user's local time (the same per-user AT TIME ZONE pattern used for daily bucketing), otherwise a user near a week boundary can be misassigned by a few hours the same way a naive UTC-date bucket misassigns daily reports.
• Cohorts with very small cohort_size (a handful of users) produce noisy percentages, a single user churning swings the rate by double-digit points; report the raw cohort_size alongside the percentage so a reader does not over-trust a 50.0% built from 2 out of 4 users.

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.