Tables customers(id int primary key, name text, city text) and orders(id int primary key, customer_id int references customers(id), amount int, ordered_on date).
Return one row per calendar month that has orders: month formatted as YYYY-MM, orders (the count) and revenue (the sum of amounts). Order by month.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: month, orders, revenue.
Example
Input
create table customers (id int primary key, name text not null, city text not null);
create table orders (id int primary key, customer_id int not null references customers(id), amount int not null, ordered_on date not null);
insert into customers values (1,'Riya','Pune');
insert into orders values (1,1,100,'2025-01-05'),(2,1,40,'2025-01-31'),(3,1,70,'2026-01-02'),(4,1,10,'2025-03-15');Output
2025-01|2|140
2025-03|1|10
2026-01|1|70January 2025 has two orders (140); March 2025 one; January 2026 is a separate month.
Show hints
- to_char(date, 'YYYY-MM') formats a date.
- date_trunc('month', ...) gives the first day of the month and sorts correctly.
Show the explanation
Group by the month. to_char(ordered_on, 'YYYY-MM') produces the label; because the format is zero-padded year-first, the text sorts in date order too. Alternatives are date_trunc('month', ordered_on) (a timestamp) or extract(year ...) and extract(month ...). Grouping by year and month together is essential — grouping only by extract(month ...) would merge January 2025 with January 2026.
Original UniCircle question. Last reviewed 22 Oct 2026.