Quartiles by Order Value — NTILE and Percentiles in SQL

The problem

Brightlane's revenue team is categorizing every order into four value tiers for routing and prioritization decisions.

Write a query to return every order's ID, customer ID, total amount, and the order's value tier across the full order set. Sort the final result by total_amount ascending.

Assumptions:

Orders are sorted by total_amount ascending and assigned to one of four tiers based on position. Tier 1 covers the lowest-value quarter of orders by row count; tier 4 covers the highest-value quarter.
When the row count does not divide evenly by 4, the earlier tiers each receive one extra record.
Two orders with identical total_amount values may land in different tiers if they fall on opposite sides of a tier boundary.
The final result is sorted by total_amount ascending.

Output:

One row per order, with columns id, customer_id, total_amount, and value_quartile. Sorted by total_amount ascending.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  customer_id,
  total_amount,
  NTILE(4) OVER (
    ORDER BY
      total_amount
  ) AS value_quartile
FROM
  orders
ORDER BY
  total_amount

The shape

NTILE(4) over an ascending total_amount sort divides the order set into four equal-row value tiers in one pass. Every order keeps its identifying columns and picks up a tier number indicating where its dollar amount sits in the overall row order. The outer sort makes the printed result line up smallest first.

Clause by clause

SELECT id, customer_id, total_amount, NTILE(4) OVER (ORDER BY total_amount) AS value_quartile returns the order's identifying columns plus its tier. The ORDER BY total_amount inside OVER defines the sort the window function walks; NTILE(4) then assigns the first quarter of rows to bucket 1 and the last quarter to bucket 4.
FROM orders reads every order. No filter, so the bucketing runs across the full order set.
ORDER BY total_amount is the outer sort that controls the printed sequence. It runs after the window function has already finished its work.

Why this and not a manual rank-then-divide

You could compute a sequential rank and derive a tier from that rank with arithmetic, but NTILE(4) answers the exact question in one expression. The window function knows the row count, the bucket count, and the sort order, and it handles the uneven-division case by giving the earlier buckets one extra row.

The trap

The bucketing is positional, not value-based. Two orders with the same total_amount that fall on opposite sides of a bucket boundary will end up in different tiers. The revenue team should read tier 1 as "the bottom quarter of orders by row count" rather than "every order under a fixed dollar threshold." If the routing logic needs a hard cutoff at a specific dollar value, the percentile functions are the right tool. NTILE is for equal-row segmentation.

You practiced NTILE(4) over a numeric column — the same shape applies to any value the analyst wants to bucketize positionally.

Return every order's ID, customer ID, total amount, and the order's value tier across the full order set. Sort the final result by `total_amount` ascending

The shape

Clause by clause

Why this and not a manual rank-then-divide

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.