Customers and Their Order Counts

Medium ⏱ 12 min 61% acceptance ★★★★★ 4.6

Return every customer with the number of orders they have placed — including customers who have never ordered (they should show a count of 0). Output name and order_count, sorted by order_count descending then name ascending.

Database Schema

Use these exact table and column names in your query. Open Schema & Data under the editor to preview sample rows.

customers 5 columns

customer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE

products 4 columns

product_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL

orders 5 columns

order_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT

order_items 4 columns

order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL

payments 5 columns

payment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE

employees 6 columns

employee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE

Examples

Example 1

Input

customers, orders tables

Output

name | order_count
Rahul | 5
... | ...
Fatima | 0

Explanation

A LEFT JOIN keeps customers with no matching orders; COUNT of the order key is 0 for them.

Constraints

Every customer must appear exactly once.
Customers with no orders show 0.

Topics

JOINGROUP BYAggregation

Companies

AmazonUberWalmart

Hints

Hint 1

Start FROM customers and LEFT JOIN orders.

Hint 2

COUNT(order_id) — not COUNT(*) — so non-matches count as 0.

Hint 3

GROUP BY the customer.

Reveal Official Solution

Try it yourself first — you learn more from a struggle than a spoiler.

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

Related Problems

← Customers From a City Calculate Monthly Revenue →

Press Run (or Ctrl+Enter) to execute your query against the sample database.

customers5 cols

customer_idINTEGER
nameTEXT
cityTEXT
countryTEXT
signup_dateDATE

customer_id	name	city	country	signup_date
101	Rahul	Delhi	India	2023-01-12
102	Aman	Mumbai	India	2023-02-03
103	Sara	Bengaluru	India	2023-02-20

products4 cols

product_idINTEGER
product_nameTEXT
categoryTEXT
priceREAL

product_id	product_name	category	price
1	Wireless Mouse	Electronics	799
2	Office Chair	Furniture	5499
3	Notebook	Stationery	149

orders5 cols

order_idINTEGER
customer_idINTEGER
order_dateDATE
amountREAL
statusTEXT

order_id	customer_id	order_date	amount	status
1001	101	2024-01-05	1598	Delivered
1002	102	2024-01-11	5499	Delivered
1003	101	2024-02-02	149	Cancelled

order_items4 cols

order_idINTEGER
product_idINTEGER
quantityINTEGER
unit_priceREAL

order_id	product_id	quantity	unit_price
1001	1	2	799
1002	2	1	5499
1004	3	5	149

payments5 cols

payment_idINTEGER
order_idINTEGER
methodTEXT
amountREAL
paid_dateDATE

payment_id	order_id	method	amount	paid_date
5001	1001	UPI	1598	2024-01-05
5002	1002	Card	5499	2024-01-11
5003	1005	UPI	999	2024-03-02

employees6 cols

employee_idINTEGER
nameTEXT
departmentTEXT
manager_idINTEGER
salaryREAL
hire_dateDATE

employee_id	name	department	manager_id	salary
1	Meera	Analytics	NULL	180000
2	Rohit	Analytics	1	95000
3	Neha	Engineering	1	120000

Loading expected output…