Window functions f4

Browser
1.5 hours

Prerequisites: Joins (f3)

Build a query in steps and compare each row with its neighbours: subqueries, common table expressions and window functions for ranks, shares and running totals. Everything runs in this browser, on the tables the Schema page describes.

Already know this?

For each species with at least 1,000 records that have a year, show its best year (most records; on a tie, the later year), that year's records, the species' dated records and the best year's share of them in percent to one decimal, largest share first. Solve it and the module is marked done.

Your answer

Show answer

WITH per_year AS (
  SELECT species_id, year, count(*) AS records
  FROM occurrences
  WHERE year IS NOT NULL
  GROUP BY species_id, year
),
ranked AS (
  SELECT species_id, year, records,
         sum(records) OVER (PARTITION BY species_id) AS dated,
         row_number() OVER (PARTITION BY species_id ORDER BY records DESC, year DESC) AS place
  FROM per_year
)
SELECT species_id, year, records, dated, round(100.0 * records / dated, 1) AS share_pct
FROM ranked
WHERE place = 1 AND dated >= 1000
ORDER BY share_pct DESC, species_id;

Concept

A query can use another query's result as a table.

A subquery is a query in brackets: in FROM it is a table (give it an alias); in WHERE or SELECT it can return one value (a scalar subquery) or a list for IN. A correlated subquery refers to the outer row (WHERE o.country_code = c.code in the exercise of Joins (f3)) and runs, in effect, once per row.
A common table expression (CTE), WITH name AS (…), names a subquery before the main query. Several CTEs, separated by commas, read top to bottom like the steps of a recipe, and each may use the ones above it.
A window function computes a value for each row from a set of related rows, its window, without collapsing them as GROUP BY does. OVER (PARTITION BY species_id ORDER BY records DESC) says which rows form the window (the same species) and in which order. row_number() numbers the rows 1, 2, 3; rank() gives ties the same number and skips after them (1, 1, 3); dense_rank() doesn't skip (1, 1, 2). An aggregate with OVER (sum(records) OVER (PARTITION BY species_id)) puts the group's total on every row, so a share is one division. With ORDER BY in the window, sum becomes a running total, and lag(x) reads the previous row's value.
Window functions run after WHERE, GROUP BY and HAVING, so they can't be filtered in WHERE: compute them in a CTE and filter outside, as in "Already know this?". They can rank aggregates (rank() OVER (ORDER BY count(*) DESC)) in the same query that groups.

Worked example

Which dataset contributed most of each species' records, and what share is that? Three steps, one CTE each: count records per species and dataset; put each species' total and a place on every row; keep place 1 and join the dataset's title.

Query f4-top-dataset

The top rows are species with a handful of records, or with one source only, so 100 % says little there; read on down. The Jaguar's sample is 97 % one GPS-collar dataset: most of its 2,000 points are positions of collared animals, not 2,000 separate sightings. Bias in records, s1, comes back to this. row_number breaks ties by dataset_key so that place 1 is always one row; with rank, a tie would keep both.

Lab

Which species have more records than the average species? A subquery in FROM counts per species; a scalar subquery in HAVING gives the average of those counts.

Query f4-above-average SELECT species_id, count(*) AS records FROM occurrences GROUP BY species_id HAVING count(*) > (SELECT avg(n) FROM (SELECT count(*) AS n FROM occurrences GROUP BY species_id) AS per_species) ORDER BY records DESC, species_id;
row_number, rank and dense_rank side by side, on records per species. Thirteen species sit at the sample's cap of 2,000 records per species (the lab data page), a tie:

Query f4-ranks SELECT species_id, count(*) AS records, row_number() OVER (ORDER BY count(*) DESC, species_id) AS by_row, rank() OVER (ORDER BY count(*) DESC) AS by_rank, dense_rank() OVER (ORDER BY count(*) DESC) AS by_dense_rank FROM occurrences GROUP BY species_id ORDER BY by_row LIMIT 16;
How does each year since 2000 rank against the others, and what share of those years' records does it hold? sum(count(*)) OVER (), a window over every row, is the total.

Query f4-years SELECT year, count(*) AS records, rank() OVER (ORDER BY count(*) DESC) AS place, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS share_pct FROM occurrences WHERE year >= 2000 GROUP BY year ORDER BY place, year;
The Tasmanian Devil (id 48) year by year since 2010: the change from the year before with lag, and a running total. ROWS UNBOUNDED PRECEDING makes the frame "every row up to this one"; it matters only when the order has ties, but saying it is clearer.

Query f4-running SELECT year, count(*) AS records, count(*) - lag(count(*)) OVER (ORDER BY year) AS change, sum(count(*)) OVER (ORDER BY year ROWS UNBOUNDED PRECEDING) AS running_total FROM occurrences WHERE species_id = 48 AND year >= 2010 GROUP BY year ORDER BY year;

A year with no records would have no row, and lag would compare with the last year that has one, not the calendar year before. A calendar of years, left-joined (f3), fills such gaps.

Result

Most species' samples lean on one or two datasets, and for the Jaguar one GPS-collar dataset is almost everything. Ranks show the cap at work: 13 species share rank 1, so rank jumps to 14 and dense_rank to 2; which you want depends on whether "third place" means "two ahead of you" or "the third distinct value". 2021 is the sample's biggest recent year, and the Devil's records fall sharply after 2010.

Exercise

For each class (species_taxonomy.class), show its three species with the most records (ties broken by the lower species_id): class, species_id, records, the place (1–3) and the species' share of its class's records in percent to one decimal, by class and place.

Your answer

Show answer

WITH per_species AS (
  SELECT t.class, o.species_id, count(*) AS records
  FROM occurrences AS o
  JOIN species_taxonomy AS t ON t.species_id = o.species_id
  GROUP BY t.class, o.species_id
),
ranked AS (
  SELECT class, species_id, records,
         row_number() OVER (PARTITION BY class ORDER BY records DESC, species_id) AS place,
         round(100.0 * records / sum(records) OVER (PARTITION BY class), 1) AS share_pct
  FROM per_species
)
SELECT class, species_id, records, place, share_pct
FROM ranked
WHERE place <= 3
ORDER BY class, place;

Check

Your rows must equal the answer's, column by column and in the same row order: the order is part of the answer here. "Already know this?" is checked the same way. Check runs the answer on the tables as loaded, so if you changed them in the playground, Reset database first.

Dialects

PostgreSQL: as here; also FILTER on window aggregates and named windows (WINDOW w AS (…)); a CTE can be MATERIALIZED or NOT MATERIALIZED; WITH RECURSIVE walks trees such as taxa. No QUALIFY.
DuckDB (this page's engine): as PostgreSQL, plus QUALIFY place = 1, which filters on a window function without a CTE, and arg_max(dataset_key, records) for "the value with the largest count" in one aggregate.
SQL Server: CTEs and window functions as here (the statement before a WITH must end in ;); no QUALIFY; TOP (1) WITH TIES … ORDER BY row_number() OVER (…) or CROSS APPLY (SELECT TOP (3) …) for top-n per group; count is int, so cast before dividing for a share.