SQL for Data Analysis Questions

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

Put the date-range condition in the ON clause, not WHERE. That keeps every account in the result, even ones with zero matching transactions in the range, as a NULL-padded row from the LEFT JOIN, which then gets COALESCEd to $0. A date filter in WHERE runs after the join and discards any account whose transactions (matched or NULL-padded) don't satisfy it, silently dropping the account from the report entirely rather than showing it at $0.

Structured elaboration

Approach: this is the same root mechanism as filtering a right-hand table's column in WHERE after a LEFT JOIN (predicate applied post-join kills NULL-padded rows), applied here to a date range instead of an equality filter. The visible symptom is worse for this specific ask: it's not that a matched row loses some detail, the account disappears from the report completely, which directly violates the requirement that zero-activity accounts must show up at $0.

Key points:

• Date range in ON: narrows which transactions count as a match, but every account still survives the join.
• Date range in WHERE: filters the combined, already NULL-padded rowset; an account with no transactions in range (whether it has transactions elsewhere, or none at all) fails the WHERE test and vanishes.
• A predicate on the LEFT table itself (accounts), like WHERE a.active = TRUE, is a different case and belongs in WHERE; don't over-correct by moving every predicate into ON.

Worked example

Seed data: 4 accounts. Acme Co has a transaction inside the range. Beta LLC has a transaction, but only outside the range. Cobalt Inc has no transactions at all, ever. Delta Group has two transactions inside the range.

Buggy, date filter in WHERE:

SELECT
  a.account_id, a.account_name,
  COALESCE(SUM(t.amount_cents), 0) / 100.0 AS revenue_usd
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE t.transaction_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY a.account_id, a.account_name
ORDER BY a.account_id;

Result (run in DuckDB):

account_id | account_name | revenue_usd
-----------+--------------+------------
         1 | Acme Co      |       100.0
         4 | Delta Group  |        50.0

Beta LLC and Cobalt Inc are simply gone. This is easy to miss in review, since the query "runs fine" and returns a plausible-looking table, just a shorter one than it should be.

Fixed, date filter moved into ON:

SELECT
  a.account_id, a.account_name,
  COALESCE(SUM(t.amount_cents), 0) / 100.0 AS revenue_usd
FROM accounts a
LEFT JOIN transactions t
  ON t.account_id = a.account_id
  AND t.transaction_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY a.account_id, a.account_name
ORDER BY a.account_id;

Result:

account_id | account_name | revenue_usd
-----------+--------------+------------
         1 | Acme Co      |       100.0
         2 | Beta LLC     |         0.0
         3 | Cobalt Inc   |         0.0
         4 | Delta Group  |        50.0

All 4 accounts appear. Beta LLC and Cobalt Inc correctly show $0.00 in the range, distinguishing "no activity in this window" from "doesn't exist," which is exactly what the requirement asked for.

Complexity

Both versions are a single scan of accounts with an index (or hash) lookup into transactions, O(n + m) with an index on transactions.account_id. The fix changes correctness, not complexity class; on most cost-based planners, a selective date predicate in ON can be pushed into the scan of transactions before the join, so there is no performance downside to the fix.

Edge cases

• An account whose only transactions fall entirely outside the range (Beta LLC): correctly shows $0.00, distinct from Cobalt Inc, which has no transactions ever. Both look identical in this report's output, which is fine for a revenue report but worth flagging if a downstream consumer needs to tell "inactive this quarter" apart from "never had an account with us."
• A predicate on accounts itself, such as excluding closed accounts, is a filter on the LEFT table and belongs in WHERE, not ON; conflating the two rules (all predicates move to ON) is itself a mistake.
• Timezone-naive date columns: if transaction_date is actually a timestamp with time components, a naive BETWEEN on date strings can silently exclude the last partial day of the range; cast explicitly or use half-open bounds (>= start AND < end + 1 day).

Trade-offs & pitfalls

• The failure mode here (accounts disappearing entirely) is more dangerous to catch in review than the S35-style version (a matched row losing detail), because the query returns a shorter, still-plausible-looking table with no error and no obviously missing row to spot; validating against COUNT(*) FROM accounts as an expected row-count floor is a cheap habit that catches it immediately.
• Resist "fixing" this by adding OR t.id IS NULL back into WHERE; it happens to work for a single date-range predicate on one column but breaks the moment a second filtered join is added to the same query.
• If a downstream consumer needs to distinguish "no activity in range but has history elsewhere" from "never had any transactions," this query as written can't do that without an additional column (e.g., a separate EXISTS check against unfiltered transactions); decide whether that distinction matters before shipping the simpler version.

Direct answer

Give every row a rank within its entity using ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY recency_column DESC), then keep only rn = 1. The recency column should be whatever proves the row is newest (an updated_at or response_date timestamp), with a deterministic tiebreaker (the row's own id) for the rare case of an exact tie.

Structured elaboration

• Why ROW_NUMBER() and not MAX()/GROUP BY: you need the whole latest row (every column), not just the max of one column. GROUP BY on the entity id alone can't return "the rest of the row that goes with the max timestamp" without a second join. ROW_NUMBER() over a subquery gets there in one pass.
• Tiebreaker: if two writes land at the exact same timestamp (a genuine retry duplicate, not a later correction), order by a secondary tiebreak column, an auto-increment id, so the result is deterministic rather than "whichever row the engine happens to return first."
• This pattern generalizes: user-profile records overwritten by a retried client call, survey responses where a user resubmitted, event streams where a later correction should supersede an earlier one. The shape, partition by entity, order by recency, keep rank 1, is identical across all of them.

Worked example

Example 1: user_profile_writes, a retried client call, tested in DuckDB 1.5:

Writes 2 and 3 share the identical timestamp, 2024-01-03 10:00:00, a genuine tie (a retried write that landed in the same second as the original), so updated_at alone cannot decide between them.

SELECT write_id, user_id, email, updated_at
FROM (
  SELECT
    write_id,
    user_id,
    email,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC, write_id DESC) AS rn
  FROM user_profile_writes
) ranked
WHERE rn = 1
ORDER BY user_id;

Result:

User 101's writes 2 and 3 are tied on updated_at. write_id DESC is what breaks the tie here, it picks write 3 over write 2. Without it, ranking the two tied rows by updated_at DESC alone is not guaranteed to pick the same one every time, since SQL does not define an order among rows that compare equal on every ORDER BY column, re-running the query without the write_id tiebreak against the same data returned write 2 as rank 1 on one run in testing.

Example 2: survey_responses, a user resubmitting:

SELECT user_id, score, response_date
FROM (
  SELECT
    user_id,
    score,
    response_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY response_date DESC, response_id DESC) AS rn
  FROM survey_responses
) ranked
WHERE rn = 1
ORDER BY user_id;

Result:

User 201's later, higher score (9) correctly supersedes their earlier response (6). This one has no tie, response_date DESC alone already picks response 2; response_id DESC is only there as a safety net for a future tie.

Trade-offs & pitfalls

• Complexity is driven by the ORDER BY inside each partition; an index on (entity_id, recency_column) lets most engines satisfy the ranking without a separate sort step.
• DISTINCT ON (Postgres and DuckDB) or QUALIFY (Snowflake/DuckDB/BigQuery) express the same "keep 1 row per group" pattern more concisely than ROW_NUMBER() plus an outer filter, but neither is available in MySQL, SQLite, or BigQuery's DISTINCT ON form, so ROW_NUMBER() is the version that is portable across every mainstream SQL engine, worth knowing even where the shorter syntax is available.
• If "latest" needs to weigh more than one signal (prefer a manually-verified record over a newer but unverified one), ROW_NUMBER()'s ORDER BY can take multiple columns, but at that point write out explicitly why each column is in the tiebreak order. An unexplained multi-column ORDER BY is a common source of "why did it pick THIS row" confusion during review.
• rn = 1 must be filtered in an outer query or CTE, not in the same SELECT as the window function itself, a window function's result is not available to a WHERE clause at the same query level.

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.

Comparing "August 1 to August 15 this year" against "August 1 to August 15 last year" compares two windows with different numbers of business days whenever the month starts on a different weekday in each year, which skews the comparison before a single number is even looked at. The fix: count how many business days have elapsed in the current period, then walk the prior year's calendar forward that same number of business days, not calendar days, to find its comparable cutoff date.

Approach

1. Build a calendar of every date across both years, and tag each as a business day (excluding Saturday/Sunday here; add a company-holiday flag too if that matters to the business).
2. Number each month's business days sequentially with ROW_NUMBER() partitioned by year-month.
3. Count how many business days have elapsed this month as of the report date, using COUNT(), not an equality lookup on the report date itself, since the report date can land on a weekend (it does in the example below).
4. Find the date in the prior year's same month whose business-day sequence number matches that count: that is the true "same point in the business cycle" cutoff.
5. Sum revenue from each month's start through its respective cutoff date.

Worked example (SQLite; dialect notes below)

CREATE TABLE transactions (
  txn_date TEXT,
  amount   NUMERIC
);

-- Current-year transactions: August 2026 (Aug 1, 2026 is a Saturday).
-- As-of date pinned to 2026-08-15 for reproducibility (never CURRENT_DATE).
INSERT INTO transactions VALUES
  ('2026-08-01', 500), ('2026-08-03', 300), ('2026-08-04', 700),
  ('2026-08-06', 200), ('2026-08-08', 900), ('2026-08-11', 400),
  ('2026-08-13', 250), ('2026-08-15', 600), ('2026-08-18', 999); -- after as-of, excluded

-- Prior-year transactions: August 2025 (Aug 1, 2025 is a Friday).
INSERT INTO transactions VALUES
  ('2025-08-01', 450), ('2025-08-04', 300), ('2025-08-06', 650),
  ('2025-08-08', 500), ('2025-08-11', 150), ('2025-08-13', 800),
  ('2025-08-15', 700), -- a business day (Friday); a naive same-calendar-date
                        -- cutoff would wrongly include it
  ('2025-08-17', 999);  -- out of range either way

WITH RECURSIVE cal(d) AS (
  SELECT date('2025-08-01')
  UNION ALL
  SELECT date(d, '+1 day') FROM cal WHERE d < date('2026-08-31')
),
-- cal builds in passes: the base row is 2025-08-01, then each pass takes the
-- previous pass's row and builds the next day from it (date(d, '+1 day')),
-- re-running against cal's own growing output, until a pass's output fails
-- the WHERE condition (d < 2026-08-31) and the recursion stops.
business_days AS (
  SELECT
    d,
    strftime('%Y-%m', d) AS ym,
    ROW_NUMBER() OVER (PARTITION BY strftime('%Y-%m', d) ORDER BY d) AS bday_num
  FROM cal
  WHERE strftime('%w', d) NOT IN ('0', '6')   -- drop Sunday(0) and Saturday(6)
),
as_of AS (
  SELECT '2026-08-15' AS as_of_date
),
n_elapsed AS (
  -- business days elapsed through (and including) the pinned as-of date;
  -- COUNT(*), not an equality lookup, because the as-of date itself can be
  -- a weekend (it is here: 2026-08-15 is a Saturday)
  SELECT COUNT(*) AS n
  FROM business_days, as_of
  WHERE ym = strftime('%Y-%m', as_of.as_of_date) AND d <= as_of.as_of_date
),
boundary_this_year AS (
  SELECT as_of.as_of_date AS boundary_date FROM as_of
),
boundary_last_year AS (
  -- the date in Aug 2025 that completes the SAME COUNT of business days
  SELECT MAX(d) AS boundary_date
  FROM business_days, n_elapsed
  WHERE ym = '2025-08' AND bday_num <= n_elapsed.n
)
SELECT
  'this_year' AS period,
  (SELECT boundary_date FROM boundary_this_year) AS mtd_boundary_date,
  (SELECT n FROM n_elapsed) AS business_days_elapsed,
  COALESCE(SUM(amount), 0) AS mtd_revenue
FROM transactions
WHERE txn_date BETWEEN '2026-08-01' AND (SELECT boundary_date FROM boundary_this_year)
UNION ALL
SELECT
  'last_year' AS period,
  (SELECT boundary_date FROM boundary_last_year) AS mtd_boundary_date,
  (SELECT n FROM n_elapsed) AS business_days_elapsed,
  COALESCE(SUM(amount), 0) AS mtd_revenue
FROM transactions
WHERE txn_date BETWEEN '2025-08-01' AND (SELECT boundary_date FROM boundary_last_year);

Result:

┌───────────┬───────────────────┬────────────────────────┬─────────────┐
│  period   │ mtd_boundary_date │ business_days_elapsed  │ mtd_revenue │
├───────────┼───────────────────┼────────────────────────┼─────────────┤
│ this_year │ 2026-08-15        │ 10                     │ 3850        │
│ last_year │ 2025-08-14        │ 10                     │ 2850        │
└───────────┴───────────────────┴────────────────────────┴─────────────┘

By August 15, 2026 (a Saturday), 10 business days had elapsed in the current month. In August 2025, the 10th business day lands on August 14, not August 15, because August 2025 started on a Friday, one weekday earlier than August 2026's Saturday start, so its weekend interruptions land differently across the first two weeks. Capping last year's window at August 14 gives $2,850. If the query had instead used the same calendar date in both years (August 15, the naive approach), last year's total would be $3,550 ($700 booked on Friday, August 15, 2025, would be wrongly included), a 24.6% larger number driven entirely by one extra business day of revenue, not by any real change in the business.

Dialect notes

Written here in SQLite (date() and strftime(), no julianday() needed for this one). Postgres: build the calendar with generate_series(date1, date2, interval '1 day') and get the weekday with EXTRACT(DOW FROM d); BigQuery: GENERATE_DATE_ARRAY(start, end) and EXTRACT(DAYOFWEEK FROM d). The business-day-counting logic itself (ROW_NUMBER() partitioned by month, filtered to weekdays) is portable across all three; only the calendar-generation and weekday-extraction syntax changes.

Trade-offs & pitfalls

• Complexity: the recursive calendar CTE is O(days in range), fine for a year or two of daily granularity but wasteful to regenerate on every report run at scale. Materialize a permanent calendar or business-day dimension table once and join to it instead.
• Edge case: the as-of date itself can fall on a non-business-day, handled here with COUNT(*) over d <= as_of_date rather than an equality match on d = as_of_date, which would silently return nothing if the as-of date is a weekend.
• Public holidays aren't handled at all here, only weekends; a real calendar dimension table should carry a holiday flag too, or this technique will overcount business days around Thanksgiving, Christmas, and similar closures.
• Common wrong turn: reaching for the same calendar date and calling it an "MTD comparison" without checking whether the two months start on the same weekday. That silent mismatch is exactly what this question is testing for.