Repeat customer rate

Easy

Question

The orders table has one row per order. The status is either delivered or cancelled. Only delivered orders count in this question.

A customer is a person with at least one delivered order. A repeat customer is a customer with two or more delivered orders.

Return exactly one row with three columns: customers (how many customers there are), repeat_customers (how many of them are repeat customers) and repeat_rate (repeat_customers as a percentage of customers, rounded to 1 decimal).

group by
conditional aggregation
percentage
round
integer division
subquery

Read first: Lesson 46, SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

orders5 columns · 15 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, amount INTEGER NOT NULL, status TEXT NOT NULL, ordered_at TEXT NOT NULL);

Example rows of the orders table
id	customer_id	amount	status	ordered_at
1	1	450	delivered	2026-03-01
2	1	320	delivered	2026-03-09
3	1	610	delivered	2026-03-20
4	2	280	delivered	2026-03-02
5	2	300	cancelled	2026-03-12
6	3	700	delivered	2026-03-03
7	3	150	delivered	2026-03-25
8	4	520	delivered	2026-03-05
9	5	400	cancelled	2026-03-06
10	5	410	cancelled	2026-03-07
11	6	260	delivered	2026-03-08
12	7	940	delivered	2026-03-10
13	7	880	delivered	2026-03-18
14	8	190	delivered	2026-03-15
15	8	220	cancelled	2026-03-16

About this SQL

In SQLite a comparison such as `n >= 2` gives 1 or 0, so SUM(n >= 2) counts the true cases, but a CASE expression is clearer and works in every database. Whole number / whole number is a whole number (3 / 7 is 0), so start the percentage with 100.0. ROUND(x, 1) keeps one decimal.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready