Ecommerce storefront

A year of orders across five joined tables, with best sellers, heavy buyers, a real Q4 peak and totals that reconcile to the cent.

An online retailer's 2025: 2,000 customers in ten countries, a 300-product catalogue, orders, line items and reviews. Every order total is exactly the sum of its own line items, so the joins and the arithmetic both hold when you check them. Around that it behaves like a shop: a few business customers place a third of the orders, cheap best sellers fill most baskets, consumers order from their phones at the weekend, reviews come from people who bought the product after it arrived, and November and December rise because there are more orders, not bigger ones.

  • ecommerce
  • retail
  • star schema
  • seasonality
01,0002,000Jan 2025Mar 2025May 2025Jul 2025Sep 2025Nov 2025Dec 2025
Rows per month in orders.order_date, counted from the file.

Explore

Every table, profiled

Each column's type, spread, empties and most common values, measured from the CSVs in the download. Switch to the first rows to see the data exactly as it sits in the file.

order_items.csv

31,031 rows · 6 columns · 2 foreign keys

order_item_idprimary key
unique on every row
31,031 distinctno empties
order_idforeign key
points to orders.order_id
11,081 distinctno empties
product_idforeign key
points to products.product_id
300 distinctno empties
quantitynumber
15
mean 2.27median 21 to 5no empties
unit_pricenumber
3.99449
mean 65.35median 31.993.99 to 516.5no empties
line_totalnumber
4.99log scale1,796
mean 148.6median 66.953.99 to 2,582.5no empties

Keys

Every join resolves

5 foreign keys, 83,305 references checked against the table each one points at. None points at a row that does not exist.

order_items

  • order_idorders.order_id0 orphans
  • product_idproducts.product_id0 orphans

orders

  • customer_idcustomers.customer_id0 orphans

reviews

  • product_idproducts.product_id0 orphans
  • customer_idcustomers.customer_id0 orphans

Checks

What we checked

  • 0 orphaned foreign keys across all 5 relationships
  • 0 orders dated before their customer signed up
  • order_total equals the sum of its line items, exactly, for every order
  • 0 products priced at or below cost (margins run 22% to 64%)
  • 0 reviews by a customer who had no delivered order of that product
  • Ratings are J-shaped (57% five-star, 6% one-star), and 36 of 226 well-reviewed products average under 3.5
  • Top 10% of customers place 35.6% of orders; the top 20% of products are on 64% of lines
  • November and December average 2.1x a January–September month while order value stays $386–456
  • 63% of prices end in .99, and no city name appears in two countries

The same checks ship in the zip as INTEGRITY.txt, so you can rerun them in any SQL engine. How we verify

Questions, answered

Worth asking it

Each answer is the query's result on these exact files. Open one for its SQL and rows, then check your own query against it.

  1. 1Which category carries the best margin, and is it the one selling most?Apparel has the best margin (56.0%); Sports & Outdoors sells the most units.
    -- Orders that were kept: cancelled and returned orders left out.
    select p.category,
           sum(oi.quantity) as units,
           round(sum(oi.line_total), 2) as revenue,
           round(100 * (1 - sum(oi.quantity * p.unit_cost) / sum(oi.line_total)), 1) as margin_pct
    from order_items oi
    join orders o on o.order_id = oi.order_id
    join products p on p.product_id = oi.product_id
    where o.status not in ('cancelled', 'returned')
    group by p.category
    order by margin_pct desc
    categoryunitsrevenuemargin_pct
    Apparel7,481474,392.8556
    Beauty10,994321,028.5354.7
    Sports & Outdoors17,489729,716.8142
    Home & Kitchen15,3831,411,409.0841
    Electronics11,2061,146,088.129.7
  2. 2How much of revenue comes from the top 10% of customers, and which segment are they?The top 10% of buying customers bring 40.3% of revenue; most are Enterprise.
    with spend as (
      select customer_id, sum(order_total) as revenue
      from orders
      where status not in ('cancelled', 'returned')
      group by customer_id
    ),
    ranked as (
      select customer_id, revenue, ntile(10) over (order by revenue desc) as decile
      from spend
    )
    select c.segment,
           count(*) as customers,
           round(sum(r.revenue), 2) as revenue,
           round(100 * sum(r.revenue) / (select sum(revenue) from spend), 1) as share_of_all_revenue_pct
    from ranked r
    join customers c on c.customer_id = r.customer_id
    where r.decile = 1
    group by c.segment
    order by revenue desc
    segmentcustomersrevenueshare_of_all_revenue_pct
    Enterprise1121,094,204.4126.8
    Small Business51370,479.469.1
    Consumer30177,933.044.4
  3. 3Which products are rated worst, and do they still sell?The worst rated, Aurea Hydrating Face Cream, Travel Size at 1.75 stars, still sold 114 units (percentile 54 of products).
    with rated as (
      select product_id, count(*) as reviews, avg(rating) as rating
      from reviews
      group by product_id
      having count(*) >= 5
    ),
    sold as (
      select product_id, units, round(100 * percent_rank() over (order by units), 0) as sales_percentile
      from (
        select oi.product_id, sum(oi.quantity) as units
        from order_items oi
        join orders o on o.order_id = oi.order_id
        where o.status <> 'cancelled'
        group by oi.product_id
      ) s
    )
    select p.product_name, p.category, r.reviews,
           round(r.rating, 2) as avg_rating, s.units as units_sold, s.sales_percentile
    from rated r
    join products p on p.product_id = r.product_id
    join sold s on s.product_id = r.product_id
    order by r.rating, r.reviews desc
    limit 10
    product_namecategoryreviewsavg_ratingunits_soldsales_percentile
    Aurea Hydrating Face Cream, Travel SizeBeauty81.7511454
    Cedar Storage ContainersHome & Kitchen92.2212156
    Verdant Hair DryerBeauty152.415465
    Trailhead Swim Shorts, BlackApparel52.4397
    Kestrel Fitness Tracker, ProElectronics402.5556494
    Stride Camping Tent, ProSports & Outdoors242.5839190
    Ashford Knit BeanieApparel132.6212758
    Nimbus Portable ChargerElectronics322.6338789
    Silkroot Vitamin C Serum, Travel SizeBeauty112.6416468
    Voltix Wireless EarbudsElectronics362.6943091
  4. 4What does the Q4 lift look like split by channel?A Q4 month against a January to September one: marketplace 1.98x the orders, web 1.82x the orders, mobile_app 1.76x the orders.
    with m as (
      select channel, extract(month from order_date) as month,
             count(*) as orders, sum(order_total) as revenue
      from orders
      where status <> 'cancelled'
      group by channel, extract(month from order_date)
    )
    select channel,
           round(avg(case when month >= 10 then orders end) / avg(case when month <= 9 then orders end), 2) as q4_order_lift,
           round(avg(case when month >= 10 then revenue end) / avg(case when month <= 9 then revenue end), 2) as q4_revenue_lift,
           round(sum(case when month <= 9 then revenue end) / sum(case when month <= 9 then orders end), 2) as jan_sep_avg_order,
           round(sum(case when month >= 10 then revenue end) / sum(case when month >= 10 then orders end), 2) as q4_avg_order
    from m
    group by channel
    order by q4_revenue_lift desc
    channelq4_order_liftq4_revenue_liftjan_sep_avg_orderq4_avg_order
    marketplace1.982.12186.49199.06
    web1.821.83497.72501.19
    mobile_app1.761.7367.36356.15

Every query runs on the files in the download as they are. ANSWERS.sql holds all of them; run it in DuckDB from the dataset's folder.

Use it

Take it, or make one shaped like yours

Rebuild it byte for byte

The zip includes schema.yaml. Run misata generate --config schema.yaml with seed 20260721 and you get the same rows.

Your own tables, your own numbers

Describe the data you need in Studio and get connected tables with the totals you state, profiled like this and checked before you download.

Premium datasets

All premium
  • Card spending each month, debit and credit

    2,500 US households at five banks and seven card issuers, two years

    Five banks' raw statement lines, with every label behind them

    Answer key

    • description
    • merchant_name
    • category
    • +6 more
    Rows
    7,627,186
    Tables
    14
    Checks
    50 of 50
    Free previewProfile and preview
  • transactions, rows per monthMay 2025 – Oct 2026

    One UAE bank, 10,000 customers, eighteen months

    A UAE bank's whole trail from KYC to STR, with the laundering nobody caught

    Answer key

    • is_suspicious
    • typology
    • ring_id
    • +7 more
    Rows
    11,711,393
    Tables
    16
    Checks
    129 of 129
    Free previewProfile and preview
  • Weekly sales, at regular price and on promotion

    Grocery chain, 10 stores, fiscal 2024 and 2025

    Two fiscal years of a grocery chain, from the shelf to the receipt

    Answer key

    • baseline_units
    • price_effect
    • promo_lift
    • +7 more
    Rows
    2,790,852
    Tables
    14
    Checks
    117 of 117
    Free preview18-question projectProfile and preview

Related free datasets

All 9

Fully synthetic. No real person, company or transaction is represented, and no production data was read to make it. CC0: use it anywhere, no attribution needed.