lesson 46 Window functions · advanced

NTILE & balanced ranking buckets

Split ordered rows into ranking buckets.

the idea

NTILE divides an ordered partition into a requested number of buckets. Bucket row counts differ by at most one, with larger buckets first. It may separate equal values; use a deterministic tie-breaker if that is acceptable.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  id,
  watch_seconds,
  NTILE(4) OVER( ORDER BY watch_seconds DESC,id) AS bucket
FROM watch_events;

Watch for: NTILE creates roughly equal row counts, not equal numeric ranges or tie-preserving ranks.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Sort the values you will bucket. NTILE splits an ordered list into roughly equal buckets. Sort watch_seconds so bucket 1 holds the longest watches.

   step-1.sql Run

   sql›

   SELECT id, watch_seconds
   FROM watch_events
   ORDER BY watch_seconds DESC, id
   LIMIT 12;

2. 2

   Assign quartile buckets. NTILE(4) labels rows 1–4 over the ordered window. Bucket edges can differ slightly when counts do not divide evenly.

   step-2.sql Run

   sql›

   SELECT
     id,
     watch_seconds,
     NTILE(4) OVER(ORDER BY watch_seconds DESC, id) AS bucket
   FROM watch_events;