Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

12

Chapter 12

Window Functions

Looking sideways without collapsing rows. Partitioning, ordering, running totals, ranks, leads, and lags.

In this chapter

22

queries, easy → advanced

  1. 001ROW_NUMBER: number each row within a groupRead
  2. 002RANK vs DENSE_RANK: understand the differenceRead
  3. 003LAG: compare each employee salary to previousRead
  4. 004LEAD: see next order total per customerRead
  5. 005SUM OVER: running total of ordersRead
  6. 006Moving average: 3-month rolling average revenueRead
  7. 007NTILE: quartile bucketing of salariesRead
  8. 008FIRST_VALUE / LAST_VALUE per groupRead
  9. 009Percent of total using window functionsRead
  10. 010Gap and Island detection with window functionsRead
  11. 011Sessionization: group events by sessionRead
  12. 012Calculate customer retention cohortsRead
  13. 013Rank all products by price globallyRead
  14. 014Running count of orders per customerRead
  15. 0157-day moving average of daily salesRead
  16. 016Percentage contribution of each product to total salesRead
  17. 017Customer order value percentile rankingRead
  18. 018Compare each sale to same day last yearRead
  19. 019Find longest streak of consecutive sales daysRead
  20. 020Employee ranking by multiple metricsRead
  21. 021Classic: find duplicate records and row to keepRead
  22. 022Compute quartiles with NTILERead