Final project · SQLite

Build a checked paid-order report

Project dataset

My saved work →

Project dataset Fresh dataset challenge →Each dataset keeps its own attempts.

Your task

Return region, order_count, paid_revenue in that order. Revenue is integer whole dollars. Sort revenue descending, then region ascending (SQLite NULL sorts first on a tie).
Use paid orders with 2026-09-01 <= ordered_at < 2026-10-01. Include unmatched and NULL customer IDs; this report does not require a customer join.
Preserve a SQL NULL region as NULL. Keep repeated amounts from different order IDs and genuine zero amounts.

Pass every output check to complete this task. Suggested active time: 10 minutes. You can keep working after that.

Required output

Return exactly these columns in this order.

regionstring or NULL
order_countnumber
paid_revenuenumber

Expected output: 4 rows. Keep the requested row order.

Values, data types and the number of copies of each row must match. A missing value is distinct from zero and from an empty string.

Preserve SQL NULL unless the task explicitly gives a replacement rule. Check JOINs for extra copies of rows.

Reconciliation checks

Eligible order count: 59; paid revenue checksum: 12833.
The sum of order_count must equal the paid cohort count before grouping.
A LEFT JOIN to customers must not change the cohort size; an INNER JOIN drops orphan orders.

Need a next step?

Hint 1: where to start

Aggregate first, then ORDER BY the named total.

Hint 2: build the method

Write down the eligible keys before aggregating. Inspect each anomaly against the output contract.

Hint 3: check your work

Use the reconciliation counts after constructing the complete output; a matching total alone does not prove the rows are correct.

How your work is checked

Every output check must pass. The score shows how much matched and helps you find mistakes; completion comes from passing all checks. Active time is recorded separately.

Passing records a correct practice output on this device. It is not a professional certification.

Source materials

84 original source records.

Data dictionary

orders.order_id integer: Unique source order ID.
orders.customer_id integer | null: May have no matching customer; never filter it out for this report.
orders.status text: paid qualifies, pending does not.
orders.amount integer: Whole dollars, including zero and repeated values.
orders.region text | null: Group NULL separately; do not invent a default.
orders.ordered_at text: ISO date for half-open month selection.
customers.customer_id integer: Unique dimension key.
customers.name text: Optional descriptive label, unused in the output.

View database setup and records

CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, status TEXT, amount INTEGER, region TEXT, ordered_at TEXT);
INSERT INTO orders VALUES
(3000,999,'pending',0,NULL,'2026-09-01'),
(3001,2,'paid',117,'West','2026-10-01'),
(3002,3,'paid',134,'North','2026-09-01'),
(3003,NULL,'paid',151,'East','2026-09-04'),
(3004,5,'paid',168,'West','2026-09-05'),
(3005,6,'paid',185,'North','2026-09-06'),
(3006,7,'pending',202,'East','2026-09-07'),
(3007,8,'paid',219,'West','2026-09-08'),
(3008,9,'paid',236,'North','2026-09-09'),
(3009,10,'paid',253,'East','2026-09-10'),
(3010,11,'paid',0,'West','2026-09-11'),
(3011,12,'paid',287,NULL,'2026-09-12'),
(3012,1,'pending',304,'East','2026-09-13'),
(3013,999,'paid',321,'West','2026-09-14'),
(3014,3,'paid',338,'North','2026-09-15'),
(3015,4,'paid',355,'East','2026-09-16'),
(3016,5,'paid',372,'West','2026-09-17'),
(3017,6,'paid',109,'North','2026-09-18'),
(3018,7,'pending',126,'East','2026-09-19'),
(3019,8,'paid',143,'West','2026-09-20'),
(3020,9,'paid',0,'North','2026-09-21'),
(3021,10,'paid',177,'East','2026-09-22'),
(3022,11,'paid',194,NULL,'2026-09-23'),
(3023,12,'paid',211,'North','2026-09-24'),
(3024,1,'pending',228,'East','2026-09-25'),
(3025,2,'paid',245,'West','2026-09-26'),
(3026,999,'paid',262,'North','2026-09-27'),
(3027,4,'paid',279,'East','2026-09-28'),
(3028,5,'paid',296,'West','2026-09-01'),
(3029,6,'paid',313,'North','2026-09-02'),
(3030,7,'pending',0,'East','2026-09-03'),
(3031,8,'paid',347,'West','2026-09-04'),
(3032,9,'paid',364,'North','2026-09-05'),
(3033,10,'paid',101,NULL,'2026-09-06'),
(3034,11,'paid',118,'West','2026-09-07'),
(3035,12,'paid',135,'North','2026-09-08'),
(3036,1,'pending',152,'East','2026-09-09'),
(3037,2,'paid',169,'West','2026-09-10'),
(3038,3,'paid',186,'North','2026-09-11'),
(3039,999,'paid',203,'East','2026-09-12'),
(3040,5,'paid',0,'West','2026-09-13'),
(3041,6,'paid',237,'North','2026-09-14'),
(3042,7,'pending',254,'East','2026-09-15'),
(3043,8,'paid',271,'West','2026-09-16'),
(3044,9,'paid',288,NULL,'2026-09-17'),
(3045,10,'paid',305,'East','2026-09-18'),
(3046,11,'paid',322,'West','2026-09-19'),
(3047,12,'paid',339,'North','2026-09-20'),
(3048,1,'pending',356,'East','2026-09-21'),
(3049,2,'paid',373,'West','2026-09-22'),
(3050,3,'paid',0,'North','2026-09-23'),
(3051,4,'paid',127,'East','2026-09-24'),
(3052,999,'paid',144,'West','2026-09-25'),
(3053,6,'paid',161,'North','2026-09-26'),
(3054,7,'pending',178,'East','2026-09-27'),
(3055,8,'paid',195,NULL,'2026-09-28'),
(3056,9,'paid',212,'North','2026-09-01'),
(3057,10,'paid',229,'East','2026-09-02'),
(3058,11,'paid',246,'West','2026-09-03'),
(3059,12,'paid',263,'North','2026-09-04'),
(3060,1,'pending',0,'East','2026-09-05'),
(3061,2,'paid',297,'West','2026-09-06'),
(3062,3,'paid',314,'North','2026-09-07'),
(3063,4,'paid',331,'East','2026-09-08'),
(3064,5,'paid',348,'West','2026-09-09'),
(3065,999,'paid',365,'North','2026-09-10'),
(3066,7,'pending',102,NULL,'2026-09-11'),
(3067,8,'paid',119,'West','2026-09-12'),
(3068,9,'paid',136,'North','2026-09-13'),
(3069,10,'paid',153,'East','2026-09-14'),
(3070,11,'paid',0,'West','2026-09-15'),
(3071,12,'paid',187,'North','2026-09-16');
CREATE TABLE customers (customer_id INTEGER, name TEXT);
INSERT INTO customers VALUES
(1,'Customer 1'),
(2,'Customer 2'),
(3,'Customer 3'),
(4,'Customer 4'),
(5,'Customer 5'),
(6,'Customer 6'),
(7,'Customer 7'),
(8,'Customer 8'),
(9,'Customer 9'),
(10,'Customer 10'),
(11,'Customer 11'),
(12,'Customer 12');

Your work

Active 0:00 · Paused 0s

SQL query

Import answer

Keep a handoff you can replay

Check your current answer, then download the output and its report. The report includes the source, answer and each check so you can review what passed and what needs work.

To replay your work, open this task with the same dataset, import your answer and check it again.

Show the reference answer

SELECT region,COUNT(*) AS order_count,SUM(amount) AS paid_revenue FROM orders WHERE status='paid' AND ordered_at>='2026-09-01' AND ordered_at<'2026-10-01' GROUP BY region ORDER BY paid_revenue DESC,region ASC;

Group the full paid date cohort, including unmatched customers and NULL regions. A customer join is not needed and must not reduce the cohort.