Window Functions and Trends

Introduction

Ordinary GROUP BY collapses rows into one per group. A window function calculates across related rows without collapsing them, so you can rank items within a group or keep a running total. SQLite supports them. The outputs are from real runs.

Loans by month

First, group by month. strftime formats a date, and '%Y-%m' gives year and month:

q("""SELECT strftime('%Y-%m', loaned_on) AS month, COUNT(*) AS loans
     FROM loans
     GROUP BY month
     ORDER BY month""")

  month  loans
2024-01      7
2024-02     10
2024-03     13

Loans increased each month. Seven plus ten plus thirteen is 30, matching the total.

Running total

q("""SELECT month, loans, SUM(loans) OVER (ORDER BY month) AS running_total
     FROM (SELECT strftime('%Y-%m', loaned_on) AS month, COUNT(*) AS loans
           FROM loans GROUP BY month)""")

  month  loans  running_total
2024-01      7              7
2024-02     10             17
2024-03     13             30

SUM(...) OVER (ORDER BY month) adds up the rows from the first month to the current one. Each row keeps its own loans value and gains a running_total. The final running total, 30, equals the number of loans.

Ranking within groups

Which book is most popular within each genre?

q("""SELECT genre, title, loans,
            RANK() OVER (PARTITION BY genre ORDER BY loans DESC) AS rnk
     FROM (SELECT b.genre, b.title, COUNT(*) AS loans
           FROM loans l JOIN books b ON b.id = l.book_id
           GROUP BY b.id)
     ORDER BY genre, rnk, title""")

  genre          title  loans  rnk
  Crime      Cold Case      6    1
  Crime    Night Train      5    2
Fiction    Paper Moons      4    1
Fiction  The Quiet Sea      4    1
   Food Cooking Basics      3    1
   Food   Soup Stories      3    1
   Home    Tidy Houses      4    1
   Home    Garden Year      1    2

• PARTITION BY genre restarts the ranking for each genre.
• RANK() gives tied rows the same rank, as seen in Fiction and Food, where two books tie at 1. ROW_NUMBER() would force an order instead. Pick the one that fits the question.
• Keep only the top of each group by wrapping this query and filtering rnk = 1.

Reading a trend carefully

The monthly loans rise from 7 to 10 to 13, but with only three months and 30 loans:

• Is it a trend or just random variation? You cannot tell yet.
• Is the data for the full month? A partly-complete month looks like a fall.
• Is there seasonality? A winter-to-spring rise may repeat every year.
• Did membership grow? More members means more loans.

State the pattern, then state what it does and does not show: "Loans rose from 7 to 13 a month between January and March, but three months is too short to call it a trend."

Try it yourself

Write a query that ranks the members by number of loans, with ties sharing a rank. Then add a column showing each member's loans as a share of the total (hint: loans * 1.0 / SUM(loans) OVER ()). Check that the shares add up to 1.

Summary

• Window functions keep every row and add a calculation across related rows, such as RANK or a running total.
• PARTITION BY restarts the window per group, and RANK gives ties the same rank.
• Read trends carefully: check complete periods, sample size and other causes before claiming a pattern.