Analytics Patterns · FLOOR · histogram buckets
46. Order value histogram
Concept: FLOOR · histogram bucketsshow
A histogram groups numbers into equal-width buckets. FLOOR(x / 100) * 100 maps every value to the start of its $100 bucket (99.99 goes to 0, 100.00 to 100); GROUP BY that expression and count. Postgres also has width_bucket(x, low, high, n), which returns the bucket number.
Example (different data):
SELECT FLOOR(minutes / 10) * 10 AS bucket_start, COUNT(*) AS calls FROM support_calls GROUP BY 1 ORDER BY 1;
Your task
Pricing wants to see how completed order values are spread out before testing a free-shipping threshold.
For completed orders, return bucket_start (the amount rounded DOWN to a multiple of 100: 0, 100, 200, ...) and order_count, one row per bucket that has orders, sorted by bucket_start.
Starting Postgres in your browser…
Checking your access…