Running totals, rankings, "top 3 per category": without window functions these need messy self-joins and subqueries. With them, each one is a single line. Here's everything you need, with real results. I ran every query in SQLite and PostgreSQL.

Video version:

The sample data

-- employees -- name dept salary -- Ann Eng 120 -- Bob Eng 100 -- Cara Eng 100 -- Dan Eng 80 -- Eve Sales 90 -- Finn Sales 75 -- Gia Sales 60

Enter fullscreen mode

Exit fullscreen mode

The big idea: GROUP BY collapses rows, OVER keeps them

GROUP BY dept squashes each department into one row.…