Home / SQL / Practice problems / Medium

Number of orders per customer

Mediumrow order matters

Show every customer's name and their number of orders (0 for none), most orders first, then by name.

Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)

Your query
Show the expected result
nameorders
Aarav Sharma5
Grace Lee5
Hiroshi Tanaka5
Sita Gurung5
Zoe Clarke5
Ava Wilson4
Isla Taylor4
Jack Murphy4
Chloe Davies3
Ethan Nguyen2
Liam Walker2
Lucas Martin2
Bikash Thapa1
Leo Rossi1
Mia Chen1
Olivia Smith1
Priya Patel1
Anjali Karki0
Kiran Rai0
Noah Brown0

20 rows

Show a solution
SELECT c.name, COUNT(o.order_id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id ORDER BY orders DESC, c.name;

SQL used

New to SQL? The free SQL course teaches everything these problems use, with a self-checking exercise in every lesson.