SQL window functions practice

SELECT first_name, country_id, bounty_usd,
       RANK() OVER (PARTITION BY country_id ORDER BY bounty_usd DESC) AS country_rank
FROM suspects
WHERE country_id IS NOT NULL;

Why it works: Window functions compute across related rows without collapsing them like GROUP BY does. RANK gives ties the same number (Chris and Felix both carry 118,000) and then skips the next one.

Other databases: Window functions work in PostgreSQL, SQL Server, Oracle, MySQL 8+ and SQLite 3.25+. MySQL 5.7 doesn't have them.

Expected output (any row order):

WITH ranked AS (
  SELECT s.first_name, s.bounty_usd, c.name AS country,
         ROW_NUMBER() OVER (PARTITION BY s.country_id ORDER BY s.bounty_usd DESC) AS rn
  FROM suspects s
  JOIN countries c ON c.country_id = s.country_id
)
SELECT country, first_name, bounty_usd
FROM ranked
WHERE rn = 1;

Why it works: This is the top-N-per-group pattern. Window results only exist after WHERE runs, so you compute them in a CTE or subquery and filter on the outside.

Other databases: Same in PostgreSQL, SQL Server, Oracle and MySQL 8+. PostgreSQL also has DISTINCT ON; Snowflake/BigQuery/DuckDB have QUALIFY.

Expected output (any row order):

SELECT country_id, first_name, first_seen,
       julianday(first_seen) - julianday(LAG(first_seen) OVER (PARTITION BY country_id ORDER BY first_seen)) AS days_since_prev
FROM suspects
WHERE country_id IS NOT NULL;

Why it works: LAG looks back one row inside the window. The first row in each country has nothing before it, so LAG returns NULL and the subtraction stays NULL.

Other databases: PostgreSQL: first_seen - LAG(first_seen) OVER (...) on DATE columns. SQL Server: DATEDIFF(day, LAG(...), first_seen). MySQL: DATEDIFF(first_seen, LAG(...)).

Expected output (any row order):

WITH daily AS (
  SELECT s.seized_date, SUM(si.quantity * c.street_value) AS seized_value
  FROM seizures s
  JOIN seizure_items si ON si.seizure_id = s.seizure_id
  JOIN contraband c ON c.item_id = si.item_id
  WHERE s.status = 'confirmed'
    AND s.seized_date >= '2024-01-01' AND s.seized_date < '2025-01-01'
  GROUP BY s.seized_date
)
SELECT seized_date, seized_value,
       SUM(seized_value) OVER (ORDER BY seized_date) AS running_total
FROM daily
ORDER BY seized_date;

Why it works: SUM() OVER (ORDER BY ...) is a running total: each row adds up every row up to and including itself. Doing the GROUP BY in a CTE first keeps the two steps easy to read.

Other databases: Same in PostgreSQL, SQL Server, Oracle, MySQL 8+. Add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit when dates repeat.

Expected output (row order matters):