Group by a value worked out from a column, and keep only the groups that matter.
What to build
A monthly sales report that shows only the strong months.
Requirements
- Group the sales by month, taken from the first 7 characters of
day. - For each month, count the sales, count the different sellers and add up the amounts.
- Keep months whose total is 1000 or more, sorted by month.
Tables
Each test fills these tables with its own rows, then runs your SQL:
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
seller TEXT NOT NULL,
day TEXT NOT NULL,
amount INTEGER NOT NULL
);
Output
Columns month, orders, sellers and total, in order of month. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).
Constraints
Days are written as YYYY-MM-DD, so the text sorts in date order.
Example
When a test starts with:
INSERT INTO sales VALUES (1, 'Rahim', '2026-01-05', 500), (2, 'Nadia', '2026-01-20', 700), (3, 'Rahim', '2026-02-03', 300), (4, 'Karim', '2026-03-01', 1000), (5, 'Karim', '2026-03-15', 50);
your SQL should print:
month orders sellers total
------- ------ ------- -----
2026-01 2 2 1200
2026-03 2 1 1050
(2 rows)
Why it works
Every sale in a month has the same first 7 characters, so grouping by them makes one group per month. HAVING then filters the groups by their total, which only exists after grouping.
Examples
CREATE TABLE sales ( id INTEGER PRIMARY KEY, seller TEXT NOT NULL, day TEXT NOT NULL, amount INTEGER NOT NULL ); INSERT INTO sales VALUES (1, 'Rahim', '2026-01-05', 500), (2, 'Nadia', '2026-01-20', 700), (3, 'Rahim', '2026-02-03', 300), (4, 'Karim', '2026-03-01', 1000), (5, 'Karim', '2026-03-15', 50);
month orders sellers total ------- ------ ------- ----- 2026-01 2 2 1200 2026-03 2 1 1050 (2 rows)
CREATE TABLE sales ( id INTEGER PRIMARY KEY, seller TEXT NOT NULL, day TEXT NOT NULL, amount INTEGER NOT NULL ); INSERT INTO sales VALUES (1, 'A', '2025-12-31', 999);
month orders sellers total ----- ------ ------- ----- (0 rows)
Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.
Hints
Hint 1
`substr(day, 1, 7)` turns `2026-01-05` into `2026-01`, the month. Group by it.
Hint 2
`COUNT(DISTINCT seller)` counts the different sellers in a group.
Hint 3
The condition on the total goes in HAVING.
Solution
Show one correct solution (try it yourself first)
SELECT substr(day, 1, 7) AS month, COUNT(*) AS orders, COUNT(DISTINCT seller) AS sellers, SUM(amount) AS total FROM sales GROUP BY substr(day, 1, 7) HAVING SUM(amount) >= 1000 ORDER BY month;