How SQL Window Functions Improved My Query Performance by 40x

I recently optimized two features in my media server: Continue Watching and Up Next. Both features need to determine the next episode a user should watch based on their watch history. It sounds like a simple problem, so my first implementation followed the most obvious approach.

I loaded every show the user had started, queried the most recently watched episode for each show, queried the next episode, and then assembled the final response in JavaScript. The code was clean, easy to understand, and all the tests passed. With a small development database, performance looked perfectly acceptable.

Everything changed once I benchmarked it against a real library containing around 66,000 media files.

The architecture simply didn't scale. Every show required another database query. Even though each individual query was fast, hundreds of small queries meant hundreds of round trips between the application and SQLite. Those round trips became the bottleneck.

The Up Next endpoint eventually took over 4 seconds to complete. My first instinct was to optimize the loops or add caching, but neither addressed the real issue. The problem wasn't JavaScript. The problem was that I was asking the database the same question over and over again.

Instead of making hundreds of queries, I rewrote the logic as a single SQL query using Common Table Expressions (CTEs) and the ROW_NUMBER() window function.

WITH latest_progress AS (
  SELECT
    media_item_id,
    MAX(episode_key) AS last_watched_key
  FROM watch_progress
  WHERE user_id = ?
  GROUP BY media_item_id
),
ranked_episodes AS (
  SELECT
    id,
    media_item_id,
    episode_key,
    season_number,
    episode_number,
    ROW_NUMBER() OVER (
      PARTITION BY media_item_id
      ORDER BY season_number, episode_number
    ) AS position
  FROM episodes
)
SELECT
  next.id AS next_episode_id,
  shows.title,
  next.season_number,
  next.episode_number
FROM ranked_episodes AS current
JOIN latest_progress
  ON current.media_item_id = latest_progress.media_item_id
 AND current.episode_key = latest_progress.last_watched_key
JOIN ranked_episodes AS next
  ON next.media_item_id = current.media_item_id
 AND next.position = current.position + 1
JOIN shows
  ON shows.id = current.media_item_id;

The query lets the database do what it's designed to do. It ranks every episode within a show, finds the user's latest watched episode, determines the next one, and returns exactly the rows the application needs. Instead of pulling data into JavaScript and figuring everything out there, the database returns the final answer.

I applied the same idea to both Continue Watching and Up Next. After benchmarking the changes on the same 66,000-file library, the improvements were significant.

The biggest improvement came from Up Next, which dropped from just over four seconds to around one tenth of a second. More importantly, the application code became much simpler. Instead of loops, maps, and repeated queries, it now executes a single SQL statement and returns the result.

This experience also changed how I think about SQL. For a long time, I mostly used it for basic SELECT, INSERT, UPDATE, and DELETE statements. I underestimated how capable modern databases are at solving relational problems. Operations like joining, grouping, filtering, ranking, and finding the next or latest record are exactly what SQL is built for.

It also reinforced a few lessons. If you're executing the same query inside a loop, stop and ask whether the database can return everything in one query instead. Measure before reaching for caching, because caching often hides the symptom instead of fixing the cause. Window functions and CTEs are incredibly powerful, but they still depend on proper indexes, so make sure the columns you filter, join, and sort on are indexed.

If you've never used SQL window functions before, they're well worth learning. They didn't just make my queries faster. They simplified the application, reduced hundreds of database round trips to a single query. Sometimes the best optimization isn't writing more code. It's writing better SQL.