Histogram of order sizes

Easy

Question

The orders table has the amount of each order in whole rupees. The shop wants a histogram: a count of orders for each size group of 500 rupees.

The groups are 0 to 499, 500 to 999, 1000 to 1499, and so on. An order of exactly 500 rupees belongs to the group that starts at 500. Name each group by its first amount: the group 500 to 999 is 500.

Return bucket_start (the first amount of the group) and orders (how many orders are in the group). Leave out groups that have no orders. Sort by bucket_start from low to high.

The order of the rows matters for this question.

histogram
group by
integer division
bucket
order by

Read first: Lesson 45, SQL groups: GROUP BY, HAVING and conditional counts

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

orders3 columns · 11 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, amount INTEGER NOT NULL);

Example rows of the orders table
id	customer_id	amount
1	1	120
2	2	350
3	3	499
4	1	500
5	4	640
6	2	999
7	5	1000
8	3	1250
9	6	2200
10	4	350
11	1	640

About this SQL

In SQLite, whole number / whole number cuts off the decimals (999 / 500 is 1), so (amount / 500) * 500 rounds an amount down to the start of its group. This is a feature here. The same is true in Postgres and SQL Server, but MySQL gives a decimal for a normal /, and you would use DIV there. The % operator gives the remainder, so amount - amount % 500 is another way.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready