Ignore NULLs in aggregates and window functions

-- Aggregates skip NULLs automatically.
WITH transactions(id, amount) AS (
  VALUES (1, 100), (2, NULL), (3, 50), (4, NULL), (5, 200)
)
SELECT
  COUNT(*)        AS total_rows,           -- counts NULLs
  COUNT(amount)   AS rows_with_amount,     -- counts non-NULL only
  SUM(amount)     AS total,                -- ignores NULL
  AVG(amount)     AS mean,                 -- ignores NULL (denominator is non-NULL count)
  MIN(amount), MAX(amount)
FROM   transactions;

-- LAG / LEAD / FIRST_VALUE / LAST_VALUE — default behavior includes NULLs.
SELECT
  day,
  price,
  LAG(price)              OVER (ORDER BY day) AS prev_price,
  LAG(price) IGNORE NULLS OVER (ORDER BY day) AS prev_non_null_price
FROM stock_prices;

-- IGNORE NULLS is supported on Snowflake, Oracle, BigQuery, Redshift, DuckDB, SQL Server (2022+).
-- Placement varies: Snowflake / Oracle / SQL Server write it after the parens (as above);
-- DuckDB / BigQuery write it inside: LAG(price IGNORE NULLS) OVER (ORDER BY day).
-- Postgres / MySQL: not natively supported. Workaround:

-- Postgres workaround: nested aggregate.
WITH numbered AS (
  SELECT *,
         CASE WHEN price IS NOT NULL THEN COUNT(price) OVER (ORDER BY day) END AS grp
  FROM   stock_prices
)
SELECT day, price, MAX(price) OVER (PARTITION BY grp ORDER BY day) AS last_non_null_price
FROM   numbered;

-- "Average rating, treating missing as the dataset's mean."
WITH stats AS (SELECT AVG(rating) AS mean FROM reviews)
SELECT AVG(COALESCE(r.rating, s.mean)) FROM reviews r CROSS JOIN stats s;

-- Forward-fill missing values (carry-forward).
-- (DuckDB / BigQuery put IGNORE NULLS inside the parens; Snowflake / Oracle after them.)
WITH prices(day, price) AS (
  VALUES (DATE '2026-04-01', 10.0),
         (DATE '2026-04-02', NULL),
         (DATE '2026-04-03', 12.5)
)
SELECT day,
       COALESCE(price, LAG(price IGNORE NULLS) OVER (ORDER BY day)) AS price_filled
FROM   prices;

-- "Conditional aggregate" — count only certain rows.
SELECT
  COUNT(*) FILTER (WHERE status = 'paid')                AS paid_count,    -- Postgres / DuckDB / SQLite
  SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END)   AS paid_revenue   -- portable
FROM orders;

-- Distinct count, ignoring NULL.
SELECT COUNT(DISTINCT email) FROM users;        -- nulls always excluded from DISTINCT count