SQL & Querying Diagnostic

FINDING

The WHERE clause filters on a column that is NULL for the rows you wanted, so the result is silently empty — no error, no warning.

WHY IT MATTERS

A predicate on a nullable column drops every row where the value is NULL, because NULL fails an equality test rather than matching it.

EVIDENCE — SUBMITTED SQL

SELECT order_id, amount
FROM orders
WHERE region = 'APAC';

SUGGESTED FIX

SELECT order_id, amount
FROM orders
WHERE region = 'APAC' OR region IS NULL;

WHAT WOULD RESOLVE THIS

Rewrite the predicate to handle NULLs explicitly (IS NULL / COALESCE) and confirm the query returns the expected rows on a task where the trap can bite.

CONFIDENCE — HIGH

How stable this conclusion is, given the number of tasks that exercised this pattern and the consistency of the result.

FINDING

COUNT and SUM run before the rows with NULL amounts are handled, so the per-user total skips the blanks and reports less revenue than actually occurred.

WHY IT MATTERS

Aggregates ignore NULLs. Without COALESCE, a missing value quietly lowers the total instead of being treated as zero.

WHAT WOULD RESOLVE THIS

Coalesce the amount to 0 before aggregating, then reassess Aggregation & GROUP BY once the totals reconcile.

CONFIDENCE — HIGH

How stable this conclusion is, given the number of tasks that exercised this pattern and the consistency of the result.

FINDING

The window uses a ROWS frame, which counts rows rather than calendar days, so days with no orders are skipped and the final day drops out.

WHY IT MATTERS

ROWS counts physical rows; RANGE counts a value interval. For a calendar window you need RANGE with an explicit interval so gaps are still included.

EVIDENCE — SUBMITTED SQL

SUM(amount) OVER (
  PARTITION BY user_id ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

SUGGESTED FIX

SUM(amount) OVER (
  PARTITION BY user_id ORDER BY day
  RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW)

WHAT WOULD RESOLVE THIS

Switch the frame to RANGE with an explicit day interval and verify the last day appears.

CONFIDENCE — HIGH

How stable this conclusion is, given the number of tasks that exercised this pattern and the consistency of the result.

FINDING

A BETWEEN on a date boundary excludes the last day's afternoon, because the upper bound resolves to midnight rather than end-of-day.

WHY IT MATTERS

Date math at boundaries is a common trap: BETWEEN d1 AND d2 stops at d2 00:00, dropping everything later that day.

WHAT WOULD RESOLVE THIS

Use a half-open interval (>= d1 AND < d2 + 1 day) and reassess Time & dates.

CONFIDENCE — HIGH

How stable this conclusion is, given the number of tasks that exercised this pattern and the consistency of the result.

FINDING

A CASE inside COUNT falls through to ELSE 0, so every row is counted instead of only the matching ones.

WHY IT MATTERS

COUNT() counts any non-NULL value — including 0. Use SUM(CASE WHEN … THEN 1 END) or drop the ELSE so non-matches stay NULL.

WHAT WOULD RESOLVE THIS

Remove the ELSE branch (or switch to SUM) and confirm the conditional count matches a hand check.

CONFIDENCE — HIGH

How stable this conclusion is, given the number of tasks that exercised this pattern and the consistency of the result.