161: SQL - Filtering, NULL, and Aggregation

Learning outcomes

write predictable SELECT queries;
reason about NULL and three-valued logic;
aggregate task data without changing meaning.

Practice

Write queries for a user's open tasks, recently completed tasks, counts by status, and users with no tasks. Test empty results, NULL timestamps, ordering ties, and stable pagination order. Explain every selected column and predicate.

Worked queries

sql
SELECT id, title, status
FROM tasks
WHERE owner_id = $1 AND status = 'open'
ORDER BY created_at DESC, id DESC
LIMIT $2;

Parameters are data, not SQL text. For counts, COUNT(*) counts rows while COUNT(completed_at) skips NULL values. For “users with no tasks,” use NOT EXISTS or a carefully understood LEFT JOIN; do not filter the right table in WHERE after an outer join unless you intend to remove unmatched rows.

Edge cases

Test a page size of zero, duplicate timestamps, no matching rows, NULL completion times, and a user with no tasks. Every paginated query needs a deterministic tie-breaker.

Checkpoint

Explain why column = NULL does not match NULL, when HAVING is needed, and why ordering by a non-unique column makes pagination unstable.

References

Window functions, CTEs, and correlated subqueries

Window functions calculate across related rows without collapsing them. This gives each owner's task a rank while retaining task columns:

sql
WITH ranked AS (
  SELECT t.*,
         row_number() OVER (
           PARTITION BY owner_id ORDER BY created_at DESC NULLS LAST, id DESC
         ) AS position
  FROM tasks AS t
  WHERE t.status = 'open'
)
SELECT id, owner_id, title, position
FROM ranked
WHERE position <= 3
ORDER BY owner_id, position;

This is the top-three-per-owner pattern. PARTITION BY resets the ranking; it is not the same as GROUP BY. A CTE improves named stages and can be recursive, but it is not automatically a materialized cache or an optimization barrier in modern PostgreSQL.

For deduplication, define which row wins instead of using an arbitrary DISTINCT:

sql
SELECT DISTINCT ON (owner_id, title) id, owner_id, title, created_at
FROM tasks
ORDER BY owner_id, title, created_at DESC NULLS LAST, id DESC;

Equivalent portable shape:

sql
SELECT id, owner_id, title
FROM (
  SELECT t.*, row_number() OVER (
    PARTITION BY owner_id, title ORDER BY created_at DESC NULLS LAST, id DESC
  ) AS duplicate_rank
  FROM tasks AS t
) AS ranked
WHERE duplicate_rank = 1;

A correlated subquery can express "owners with a task above their own average":

sql
SELECT t.id, t.owner_id, t.title
FROM tasks AS t
WHERE t.priority > (
  SELECT avg(other.priority)
  FROM tasks AS other
  WHERE other.owner_id = t.owner_id
);

The inner query is logically evaluated per outer row and can be expensive; compare it with a grouped CTE using EXPLAIN. Test ties, NULL priorities, duplicate titles, owners with no rows, and an empty top-N result. Interview question: when should you use row_number, rank, or dense_rank? Use row_number for exactly N rows, rank when ties share rank with gaps, and dense_rank when ties share rank without gaps.