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.

Direct answer

"Retention" and "churn" are two sides of the same measurement, but each can be defined from either of two different signals: a contractual signal (is the subscription still active or cancelled) or a behavioral signal (did the user actually use the product). The two definitions do not just differ semantically, they can disagree on specific individual users, so picking one changes who counts as retained or churned, not only the reported percentage.

Structured elaboration

Trade-offs of each:

• Contractual: cheap to compute (one status/timestamp column), matches billing and revenue reporting directly, but misses "zombie" users who keep paying but stopped using the product (a silent-churn risk invisible until renewal), and misses "grace period" users who cancelled but keep logging in until the paid period actually ends.
• Behavioral: reflects real engagement and catches disengagement earlier (useful for a re-engagement intervention), but requires picking an inactivity window and an "active event" definition, and can flag someone as churned who is still a paying customer, just on a break.

Worked example

Sample data, tested in DuckDB 1.5:

subscriptions:

events (product usage):

Definition 1, contractual churn for February 2024:

WITH base AS (
  SELECT COUNT(*) AS active_start_of_month
  FROM subscriptions
  WHERE status = 'active' OR cancelled_at >= DATE '2024-02-01'
),
churned AS (
  SELECT COUNT(*) AS churned_users
  FROM subscriptions
  WHERE cancelled_at >= DATE '2024-02-01' AND cancelled_at < DATE '2024-03-01'
)
SELECT b.active_start_of_month, c.churned_users,
       ROUND(100.0 * c.churned_users / b.active_start_of_month, 2) AS contractual_churn_pct
FROM base b, churned c;

Result: active_start_of_month = 5, churned_users = 2, contractual_churn_pct = 40.0.

Definition 2, behavioral churn (active in January, zero events in February):

WITH jan_active AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_time >= DATE '2024-01-01' AND event_time < DATE '2024-02-01'
),
feb_active AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_time >= DATE '2024-02-01' AND event_time < DATE '2024-03-01'
)
SELECT COUNT(*) AS jan_active_users,
       COUNT(*) FILTER (WHERE f.user_id IS NULL) AS churned_users,
       ROUND(100.0 * COUNT(*) FILTER (WHERE f.user_id IS NULL) / COUNT(*), 2) AS behavioral_churn_pct
FROM jan_active j
LEFT JOIN feb_active f ON f.user_id = j.user_id;

Result: jan_active_users = 5, churned_users = 2, behavioral_churn_pct = 40.0.

Both land on 40% for this month, but a per-user comparison shows they disagree on WHO churned:

User 3 (subscription still "active," zero February activity) is churned only under the behavioral definition. User 4 (cancelled subscription, but still logging in during a grace period) is churned only under the contractual definition.

Trade-offs & pitfalls

• The two definitions can land on the same aggregate rate by coincidence (40% both, in this example) while disagreeing on which individual users churned. Never assume "the numbers match" means "the definitions agree."
• The same ambiguity applies to "inactive" and "reactivated" users: an "inactive" user could mean subscription-cancelled or simply zero recent events, and a "reactivated" user could mean a resumed subscription or a return to activity after a behavioral-inactivity flag. Each needs the same clarifying pass before it gets implemented, using whichever of the two lenses above the downstream decision actually needs.
• FILTER (WHERE ...) is Postgres/DuckDB syntax; the portable fallback is SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Direct answer

NULL means "unknown," not a value, so it doesn't equal anything, not even another NULL. col = NULL always evaluates to NULL (treated as false in WHERE), which is why col != 'x' also silently drops rows where col is NULL: NULL != 'x' is NULL, not true. You have to test for it explicitly with IS NULL / IS NOT NULL. COALESCE(expr1, expr2, ...) returns the first non-NULL argument, commonly used to substitute a default.

Structured elaboration

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL (=, !=, <, >) evaluates to UNKNOWN, and a WHERE clause only keeps rows where the condition is TRUE, so UNKNOWN rows are silently dropped, exactly like FALSE rows, with no error.

Aggregates mostly ignore NULLs rather than propagating the UNKNOWN logic: SUM, AVG, MAX, MIN skip NULL values, and COUNT(column) counts only non-NULL values in that column, while COUNT(*) counts rows regardless of NULLs.

Worked example (sqlite3, verified)

Sample data:

CREATE TABLE users (user_id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES (1, 'alice@x.com'), (2, NULL), (3, 'carol@x.com');

CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 1, 100), (2, 1, NULL), (3, 2, 50), (4, 3, 75), (5, 3, NULL);

= NULL versus IS NULL:

SELECT COUNT(*) FROM users WHERE email = NULL;    -- 0 rows
SELECT COUNT(*) FROM users WHERE email IS NULL;    -- 1 row

Verified: 0 matches for = NULL, 1 match for IS NULL (user 2).

!= silently drops the NULL row:

SELECT user_id, email FROM users WHERE email != 'alice@x.com';

Result: only (3, 'carol@x.com'). User 2 (NULL email) is not "not equal to alice," it's UNKNOWN, so it never appears, even though intuitively a missing email is certainly different from a specific address.

COUNT(*) vs COUNT(column):

SELECT COUNT(*) AS row_count, COUNT(amount) AS non_null_amount_count, SUM(amount) AS total
FROM payments;

Result: (5, 3, 225). There are 5 payment rows, but only 3 have a non-NULL amount, and SUM adds only those 3 (100 + 50 + 75 = 225), silently ignoring the 2 NULLs rather than making the whole sum NULL.

COALESCE for defaults:

SELECT payment_id, amount, COALESCE(amount, 0) AS amount_or_zero FROM payments ORDER BY payment_id;

Result: payments 2 and 5 (NULL amount) show amount_or_zero = 0, everything else passes through unchanged.

NULL behavior after a LEFT JOIN (fan-out and double counting). Aggregating right after a multi-table LEFT JOIN is a common trap when a customer has more than one matching row on another joined table. Sample data:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (101, 1, 50), (102, 1, 30), (103, 2, 20);

CREATE TABLE refunds (refund_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO refunds VALUES (201, 1, 10), (202, 1, 5);

Alice has 2 orders and, separately, 2 refunds. Joining both to customers in one query multiplies her order rows by her refund count:

SELECT c.customer_id, COUNT(o.order_id) AS order_rows_seen, SUM(o.amount) AS sum_order_amount_wrong
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN refunds r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;

Result: Alice shows order_rows_seen = 4 and sum_order_amount_wrong = 160, double the true values (2 orders, $80), because each of her 2 orders got paired with each of her 2 refunds (2x2 = 4 rows). Bob (1 order, 0 refunds, so no fan-out) correctly shows 1 and 20.

Fix, pre-aggregate orders before joining anything else:

WITH order_agg AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS sum_order_amount
  FROM orders GROUP BY customer_id
)
SELECT c.customer_id, oa.order_count, oa.sum_order_amount
FROM customers c LEFT JOIN order_agg oa ON oa.customer_id = c.customer_id;

Result: Alice correctly shows (1, 2, 80). Note AVG(o.amount) in the broken version happened to still read 40.0, the same as the correct average, because Alice's duplication factor was uniform (every order duplicated exactly twice); that's a coincidence of this data, not something to rely on. COUNT and SUM are the numbers that reveal the bug reliably.

Trade-offs & pitfalls

• Never write = NULL or != NULL; both silently return zero rows and produce no error, which makes this bug easy to ship unnoticed.
• NOT IN (subquery) is a related NULL trap: if the subquery returns even one NULL, NOT IN returns no rows at all for every outer row, because comparing against a NULL element makes the whole NOT IN expression UNKNOWN. Prefer NOT EXISTS when the inner column can contain NULLs.
• When aggregating after joining multiple one-to-many relationships in a single query, pre-aggregate each one-to-many table separately (as in the fix above) before joining, or use COUNT(DISTINCT ...) as a partial guard, though DISTINCT alone won't fix a corrupted SUM.
• GROUP BY treats all NULLs as one group (they're considered equal for grouping purposes, unlike in a WHERE ... = NULL comparison), and DISTINCT does the same, collapsing multiple NULLs into a single NULL row.

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

The four common bucketing grains, daily, weekly, monthly, and fiscal, trade noise reduction for how much timing detail survives. The choice should follow the decision the analysis actually supports (does a stakeholder act on a daily blip, a weekly trend, or a monthly close), not just "smaller bucket equals more precise." Each grain also has a specific boundary pitfall that silently distorts comparisons if the bucket definition isn't made explicit: which day starts the week, how many days are in the month being compared, and which timezone's calendar day a timestamp belongs to.

Grain, natural use case, and pitfall

Timezone is a cross-cutting concern under all four grains, not its own row: any calendar-day boundary (daily, weekly, monthly, or fiscal) can flip depending on which timezone's midnight you use to draw the line.

Worked example (executed, SQLite): three pitfalls, demonstrated

1. Week boundary. The same 6 click events, bucketed two equally defensible ways: Sunday-start weeks versus Monday-start (ISO-style) weeks.

-- Sunday-start
SELECT date(click_date, '-' || strftime('%w', click_date) || ' days') AS week_start_sun,
       COUNT(*) AS clicks
FROM clicks GROUP BY week_start_sun ORDER BY week_start_sun;

-- Monday-start
SELECT date(click_date, 'weekday 0', '-6 days') AS week_start_mon,
       COUNT(*) AS clicks
FROM clicks GROUP BY week_start_mon ORDER BY week_start_mon;

Clicks seeded on 2026-07-16 through 2026-07-21 (a Thursday through a Tuesday, crossing the Sat/Sun boundary):

Sunday-start weeks:
('2026-07-12', 3)   -- Thu, Fri, Sat
('2026-07-19', 3)   -- Sun, Mon, Tue

Monday-start weeks:
('2026-07-13', 4)   -- Thu, Fri, Sat, Sun
('2026-07-20', 2)   -- Mon, Tue

Same 6 events, same date range, but a 3/3 split under one convention becomes a 4/2 split under the other, purely because Sunday (2026-07-19) is counted as the start of a new week under one convention and the tail of the prior week under the other. Neither number is wrong; they answer different questions, and a report that doesn't state its convention is ambiguous.

2. Month length. Daily revenue is seeded at a perfectly flat $100/day for all of February (28 days in 2026, not a leap year) and all of March (31 days):

Raw monthly totals:
('2026-02', 28, 2800.0)
('2026-03', 31, 3100.0)

Normalized by day count:
('2026-02', 28, 2800.0, 100.0)
('2026-03', 31, 3100.0, 100.0)

The raw totals show March up 10.7 percent over February ((3100-2800)/2800), which looks like real growth. The average-daily-revenue column shows the truth: flat at exactly $100/day both months. The entire "growth" was February simply having 3 fewer days, not more revenue per day.

3. Timezone. Two signups stored in UTC, one at 02:30 and one at 14:00 on 2026-07-19, bucketed by UTC calendar day versus a fixed US-Pacific offset (UTC-7 in July, PDT):

UTC calendar day:
(1, '2026-07-19 02:30:00', '2026-07-19')
(2, '2026-07-19 14:00:00', '2026-07-19')

Pacific calendar day (fixed -7 hour offset):
(1, '2026-07-19 02:30:00', '2026-07-18')
(2, '2026-07-19 14:00:00', '2026-07-19')

Signup 1 lands on 2026-07-19 under UTC bucketing but 2026-07-18 under Pacific bucketing, because 02:30 UTC is still the previous evening on the US west coast. If "daily signups" is reported in UTC while the product team reasons about days in Pacific time (or vice versa), the two teams will disagree about which day a borderline signup happened on, and neither is making an arithmetic mistake.

Trade-offs and pitfalls

The single practical fix for all three pitfalls is the same: state the bucket definition explicitly in the report or query itself (which day starts the week, which timezone's calendar day, which fiscal calendar), rather than relying on a database default or an unstated team convention. A common wrong turn is assuming a smaller bucket is strictly more informative; daily buckets on a metric with strong weekday seasonality can mislead a stakeholder into reacting to normal Monday-vs-Saturday variation as if it were a real signal, when a 7-day rolling window or explicit day-of-week control would show the actual trend more honestly.