Dataset

Ecommerce Orders SQL Practice Dataset

Customers, products, orders, line items, payments, and returns at realistic online-retail volume.

Tables: 7
Rows: 426,024
Size: Large
Level: Intermediate
Domain: Ecommerce
Formats: Parquet · Schema SQL
In browser: 13,153 rows in the browser

Download this dataset, free

No account, no email, no attribution required: dedicated to the public domain under CC0 1.0, so commercial use and redistribution are fine. Open it in the browser playground, or download the Parquet files below and run ecommerce_orders.schema.sql in DuckDB to create the tables and load them.

Open in playground → 15 questions with answers ↓

ecommerce_orders.schema.sql All 7 tables, 426,024 rows, as CREATE TABLE and COPY statements that load the files below. Download →

This is a large dataset. The downloads here are the complete 426,024 rows as Parquet. Opening it in the playground loads a 13,153-row sample instead, so the browser tab stays responsive. The schema is identical, so a query you write against the sample runs unchanged against the full download.

categories.parquet 24 rows customers.parquet 12,000 rows products.parquet 2,000 rows orders.parquet 90,000 rows order_items.parquet 215,000 rows payments.parquet 95,000 rows returns.parquet 12,000 rows

This is the order book of a mid-sized online garden retailer over three years: who bought what, what they paid, what was shipped, and what came back. It is built to behave like a real one. A fifth of the customers place most of the orders, a handful of products account for a disproportionate share of the line items, and November and December carry a visible lift with a sharp spike on Black Friday.

The reason to practice here rather than on a tidy six-table sample is that this schema punishes the obvious query. An order has many line items, may have more than one payment attempt, and may have returns against some of its items. Join all four and every revenue number you compute is wrong, not by a rounding error but by a multiple. Getting the right answer means aggregating each grain separately before combining them, which is the single most common source of quietly incorrect business reporting.

The mechanism is worth stating plainly, because it is invisible in the result: a join does not fetch a child row, it produces one output row per matching pair. An order with three line items and one payment comes back as three rows. Give that same order a second payment row (a retry after a decline, or a later failed charge) and it comes back as six, because each line item is paired with each payment. Nothing is corrupted and no row is a duplicate of another; the line items are listed twice, and sum() cannot tell the difference.

Returns behave differently. A line item has at most one return, so joining returns multiplies nothing on its own. It is still not safe to add: pull it into the same query and the refund total comes out too high anyway, because a returned item sitting on a two-payment order was already duplicated by the payments join, and its refund is now counted twice. One fan-out inflates every measure downstream of it, not only the one you were watching.

The advanced practice question below makes it concrete: it computes revenue the naive way and the correct way in one query and prints the gap between them. The general forms of this are why a many-to-many join inflates totals, for the case here where a parent has two independent children, and why COUNT or SUM is too high after a join, for the simpler one-to-many case. The fix in both is the same: collapse each child to one row per parent before you combine them.

The data is deliberately imperfect in ways you will meet again. Cancelled orders never ship, so shipped_at is null rather than zero. Customers who never joined the loyalty program have a null tier, which is not the same as a tier called "none". Line items record the price at the time of sale, so they drift from the product's current list price and any report that reads products.unit_price instead will disagree with the invoice. A few customers signed up twice with the same email, so the primary key is unique while the natural key is not. None of this is noise: each one is a question waiting to be asked.

All of it is synthetic. No real customer, order, product, or payment is represented, and nothing here is derived from a proprietary source.

Schema

Arrows point from a column to the key it references. PK marks a table's key and FK a column that references another table.

Join conditions

categories.parent_category_id = categories.category_id
products.category_id = categories.category_id
orders.customer_id = customers.customer_id
order_items.order_id = orders.order_id
order_items.product_id = products.product_id
payments.order_id = orders.order_id
returns.order_item_id = order_items.order_item_id

Schema · Ecommerce Orders View dataset schema 7 tables

Table · categories

Product categories. Six roots with children beneath them; a root has no parent.

3 columns · 24 rows

category_id int category_name text parent_category_id int

Table · customers

One row per customer account. A small number share an email with another account, and loyalty_tier is null for customers who never enrolled.

9 columns · 1163 rows

customer_id int first_name text last_name text email text city text country text signup_date date loyalty_tier text marketing_opt_in bool

Table · products

One row per product, with its current list price. discontinued_at is null for products still on sale.

7 columns · 980 rows

product_id int sku text product_name text category_id int unit_price double unit_cost double discontinued_at date

Table · orders

One row per order. shipped_at is null while an order is unshipped or cancelled.

8 columns · 2400 rows

order_id int customer_id int ordered_at timestamp shipped_at timestamp status text channel text shipping_country text coupon_code text

Table · order_items

One row per product on an order. unit_price is the price at the time of sale, which drifts from the product's current price.

6 columns · 5702 rows

order_item_id int order_id int product_id int quantity int unit_price double discount_amount double

Table · payments

Payment attempts against an order. A failed attempt is followed by a retry, so an order can have more than one row.

7 columns · 2549 rows

payment_id int order_id int method text amount double paid_at timestamp status text failure_reason text

Table · returns

Returned line items. Only a minority of orders have any, so an inner join here drops most of the business.

6 columns · 335 rows

return_id int order_item_id int returned_at timestamp reason text refund_amount double restocked bool

Best for

Aggregating before joining, so line items and payments stop double-counting revenue.
Reports over three years of orders with real weekday and seasonal shape.
NULL handling for unshipped orders, unenrolled customers, and orders with no returns.

Starter questions

Which categories produce the most revenue after returns?
How much did each month bill, and how does that compare to the month before?
Which customers have never placed an order?

Practice questions

Work these in the playground against the browser sample. Each link opens the dataset with the question in the editor, ready for your query. Solutions are collapsed, so open one only after you have an answer of your own.

Beginner

1. How many orders are in the dataset, and how many distinct customers placed them?

COUNT versus COUNT DISTINCT on the same table.

Try it in the playground →
Show solution
```
select count(*) as orders, count(distinct customer_id) as customers
from orders;
```
2. How many orders are in each status, most common first?

GROUP BY with ORDER BY on the aggregate.

Try it in the playground →
Show solution
```
select status, count(*) as orders
from orders
group by status
order by orders desc;
```
3. Which orders have not shipped yet? Return the order id, status, and order date.

IS NULL, and why it is not the same as = ''.

Try it in the playground →
Show solution
```
select order_id, status, ordered_at
from orders
where shipped_at is null
order by ordered_at;
```
4. What are the ten most expensive products still on sale?

Filtering on a nullable column, then Top-N.

Try it in the playground →
Show solution
```
select product_name, unit_price
from products
where discontinued_at is null
order by unit_price desc
limit 10;
```
5. How many customers never enrolled in the loyalty program?

Counting NULLs as a category rather than filtering them away.

Try it in the playground →
Show solution
```
select count(*) as unenrolled
from customers
where loyalty_tier is null;
```

Intermediate

1. What is the gross revenue of each top-level category? Roll child categories up into their parent.

A self-join on the category hierarchy before aggregating.

Try it in the playground →

Show solution

select coalesce(parent.category_name, child.category_name) as top_category,
       round(sum(i.quantity * i.unit_price - i.discount_amount), 2) as revenue
from order_items i
join products p on p.product_id = i.product_id
join categories child on child.category_id = p.category_id
left join categories parent on parent.category_id = child.parent_category_id
group by top_category
order by revenue desc;

2. Which customers have never placed an order?

An anti-join. LEFT JOIN ... IS NULL and NOT EXISTS both work.

Try it in the playground →

Show solution

select c.customer_id, c.first_name, c.last_name
from customers c
where not exists (
  select 1 from orders o where o.customer_id = c.customer_id
)
order by c.customer_id
limit 50;

3. What was gross revenue by month? Return the month and the total, oldest first.

Truncating a timestamp to a month and grouping on it.

Try it in the playground →

Show solution

select date_trunc('month', o.ordered_at) as month,
       round(sum(i.quantity * i.unit_price - i.discount_amount), 2) as revenue
from orders o
join order_items i on i.order_id = o.order_id
group by month
order by month;

4. Which products have been sold at a price different from their current list price, and how many times?

Joining to the snapshot instead of trusting the dimension's current value.

Try it in the playground →

Show solution

select p.product_name, p.unit_price as list_price, count(*) as sales_at_other_price
from order_items i
join products p on p.product_id = i.product_id
where i.unit_price <> p.unit_price
group by p.product_name, p.unit_price
order by sales_at_other_price desc
limit 20;

5. Which email addresses belong to more than one customer account?

HAVING on a natural key that is not the primary key.

Try it in the playground →
Show solution
```
select email, count(*) as accounts
from customers
group by email
having count(*) > 1
order by accounts desc, email;
```

Advanced

1. Compute total revenue two ways: the naive way, joining orders to order_items and payments in one query, and the correct way, aggregating each child table to one row per order first. Return both totals, the amount the naive query overstates, and by what percentage.

The fan-out this dataset exists to teach. An order with two payment attempts returns each of its line items twice, so those items are summed twice: the total is inflated, not rounded.

Try it in the playground →

Show solution

-- Naive: orders joined to BOTH child tables at once.
with naive_fan_out as (
  select sum(i.quantity * i.unit_price - i.discount_amount) as revenue,
         count(i.order_item_id) as rows_summed
  from orders o
  left join order_items i on i.order_id = o.order_id
  left join payments p on p.order_id = o.order_id
),
-- Correct: collapse each child to one row per order before combining.
per_order as (
  select order_id,
         sum(quantity * unit_price - discount_amount) as order_revenue
  from order_items
  group by order_id
),
correct as (
  select sum(order_revenue) as revenue,
         (select count(*) from order_items) as rows_summed
  from per_order
)
select round(n.revenue, 2) as naive_revenue,
       round(c.revenue, 2) as correct_revenue,
       round(n.revenue - c.revenue, 2) as overstated_by,
       round(100.0 * (n.revenue - c.revenue) / c.revenue, 1) as overstated_pct,
       n.rows_summed as naive_rows_summed,
       c.rows_summed as actual_line_items
from naive_fan_out n
cross join correct c;

2. For each order month, what is gross revenue net of any refunds later issued against those orders? A month with no returns must still appear.

Aggregating two grains separately, then a LEFT JOIN so empty months survive.

Try it in the playground →

Show solution

with gross as (
  select date_trunc('month', o.ordered_at) as month,
         sum(i.quantity * i.unit_price - i.discount_amount) as gross_revenue
  from orders o
  join order_items i on i.order_id = o.order_id
  group by month
),
refunds as (
  select date_trunc('month', o.ordered_at) as month,
         sum(r.refund_amount) as refunded
  from returns r
  join order_items i on i.order_item_id = r.order_item_id
  join orders o on o.order_id = i.order_id
  group by month
)
select g.month,
       round(g.gross_revenue, 2) as gross_revenue,
       round(coalesce(f.refunded, 0), 2) as refunded,
       round(g.gross_revenue - coalesce(f.refunded, 0), 2) as net_revenue
from gross g
left join refunds f on f.month = g.month
order by g.month;

3. For each signup month, what share of customers went on to place at least two orders?

Cohorting on one table's date while counting behavior in another.

Try it in the playground →

Show solution

with per_customer as (
  select c.customer_id,
         date_trunc('month', c.signup_date) as cohort,
         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, cohort
)
select cohort,
       count(*) as customers,
       round(100.0 * count(*) filter (where orders >= 2) / count(*), 1) as repeat_pct
from per_customer
group by cohort
order by cohort;

4. How many orders have both a failed and a captured payment attempt, and what is their total captured value?

Reasoning about multiple rows per parent without inferring attempt order or multiplying the sum.

Try it in the playground →

Show solution

with by_order as (
  select order_id,
         count(*) filter (where status = 'failed') as failures,
         count(*) filter (where status = 'captured') as captures,
         max(amount) filter (where status = 'captured') as captured_amount
  from payments
  group by order_id
)
select count(*) as orders_with_both_statuses,
       round(sum(captured_amount), 2) as captured_value
from by_order
where failures > 0 and captures > 0;

5. What is the best-selling product by revenue within each category?

Top-1 per group, using a window function after revenue has been grouped by product.

Try it in the playground →

Show solution

select category_name, product_name, revenue
from (
  select c.category_name,
         p.product_name,
         round(sum(i.quantity * i.unit_price - i.discount_amount), 2) as revenue,
         row_number() over (
           partition by c.category_id
           order by sum(i.quantity * i.unit_price - i.discount_amount) desc,
                    p.product_id
         ) as rank
  from order_items i
  join products p on p.product_id = i.product_id
  join categories c on c.category_id = p.category_id
  group by c.category_id, c.category_name, p.product_id, p.product_name
)
where rank = 1
order by revenue desc;

Practice with this dataset

Guided lessons for this dataset are still being written. In the meantime, open it in the playground: the tables are already loaded, and the starter query is waiting in the editor.

Open Ecommerce Orders in the playground →

Small · 6 tables

Garden Shop SQL Practice Dataset

Orders, products, inventory, suppliers, and customers for store-operations SQL practice.

Small · 5 tables

Website Analytics SQL Practice Dataset

Sessions, pageviews, events, users, and campaigns for funnel and attribution practice.

Large · 5 tables

Marketing Campaigns SQL Practice Dataset

Channels, campaigns, ad groups, and two years of daily performance where every metric is a ratio.

Large · 5 tables

Banking Transactions SQL Practice Dataset