SQL & Querying

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.

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

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.

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.

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

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

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.

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.

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

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

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

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

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

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

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