Each Customer's First Order

Hard ⏱ 18 min 36% acceptance ★★★★★ 4.7

For each customer who has ordered, return the details of their first order (earliest order_date, breaking ties by smallest order_id). Output customer_id, order_id, order_date, ordered by customer_id. Use a CTE with a window function.

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

orders table

Output

customer_id | order_id | order_date
101 | 1001 | 2024-01-05
102 | 1002 | 2024-01-11 ...

Explanation

ROW_NUMBER() partitioned by customer, ordered by date, keep row number 1.

Constraints

Exactly one row per customer who has ordered.
Earliest order wins; ties broken by order_id.

Topics

Window FunctionsCTE

Companies

UberSwiggyZomato

Hints

Hint 1

CTE: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn.

Hint 2

Then filter rn = 1.

Reveal Official Solution

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

WITH ranked AS (
  SELECT customer_id, order_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

Related Problems

← Running Total of Revenue Repeat Customer Retention →

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…