E-commerce sample database
Customers, orders, products and line items from an online store, plus scenario tables.
20 tables · 202 rows · 96 KB
Tables
active_users 3 rows
- user_id integer PK
- name text
- email text
| user_id | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Charlie | [email protected] |
category_revenue 6 rows
- id integer PK
- category text
- year integer
- total_revenue real
| id | category | year | total_revenue |
|---|---|---|---|
| 1 | Electronics | 2023 | 500000.0 |
| 2 | Electronics | 2024 | 600000.0 |
| 3 | Furniture | 2023 | 300000.0 |
customers 12 rows
- customer_id integer PK
- name text
- email text
- city text
- signup_date text
| customer_id | name | city | signup_date | |
|---|---|---|---|---|
| 101 | Alice Mitchell | [email protected] | San Francisco | 2024-01-15 |
| 102 | Bob Chen | [email protected] | New York | 2024-02-20 |
| 103 | Carol Davis | [email protected] | Los Angeles | 2024-03-10 |
daily_sales 15 rows
- sale_date text PK
- daily_revenue real
| sale_date | daily_revenue |
|---|---|
| 2025-01-01 | 1000.0 |
| 2025-01-02 | 1200.0 |
| 2025-01-03 | 900.0 |
game_results 15 rows
- game_id integer PK
- player_id integer
- game_date text
- result text
| game_id | player_id | game_date | result |
|---|---|---|---|
| 1 | 1 | 2025-01-01 | win |
| 2 | 1 | 2025-01-02 | win |
| 3 | 1 | 2025-01-03 | win |
inactive_users 2 rows
- user_id integer PK
- name text
- email text
| user_id | name | |
|---|---|---|
| 4 | Carol | [email protected] |
| 5 | Dave | [email protected] |
missing_orders 7 rows
- order_id integer PK
- customer_name text
- order_date text
| order_id | customer_name | order_date |
|---|---|---|
| 1 | Alice | 2025-01-01 |
| 2 | Bob | 2025-01-02 |
| 4 | Carol | 2025-01-04 |
monthly_product_revenue 9 rows
- id integer PK
- product_name text
- month text
- revenue real
| id | product_name | month | revenue |
|---|---|---|---|
| 1 | MacBook Pro | Jan | 25000.0 |
| 2 | MacBook Pro | Feb | 30000.0 |
| 3 | MacBook Pro | Mar | 28000.0 |
monthly_sales 9 rows
- id integer PK
- product_name text
- sale_month text
- revenue real
| id | product_name | sale_month | revenue |
|---|---|---|---|
| 1 | MacBook Pro | 2025-01 | 25000.0 |
| 2 | MacBook Pro | 2025-02 | 30000.0 |
| 3 | MacBook Pro | 2025-03 | 28000.0 |
order_items 19 rows
- id integer PK
- order_id integer → orders
- product_id integer → products
- quantity integer
| id | order_id | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 3 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 2 | 6 | 1 |
orders 15 rows
- order_id integer PK
- customer_id integer → customers
- order_date text
- total_amount real
- status text
| order_id | customer_id | order_date | total_amount | status |
|---|---|---|---|---|
| 1 | 101 | 2024-01-20 | 249.99 | delivered |
| 2 | 101 | 2024-03-15 | 1999.99 | delivered |
| 4 | 101 | 2024-09-05 | 79.99 | delivered |
product_inventory 4 rows
- product_id integer PK
- product_name text
- current_stock integer
- avg_daily_sales real
- lead_time_days integer
- safety_stock integer
| product_id | product_name | current_stock | avg_daily_sales | lead_time_days | safety_stock |
|---|---|---|---|---|---|
| 1 | Widget A | 500 | 25.0 | 7 | 50 |
| 2 | Widget B | 300 | 40.0 | 10 | 100 |
| 3 | Widget C | 200 | 15.0 | 5 | 30 |
products 20 rows
- product_id integer PK
- product_name text
- category text
- price real
- rating real
| product_id | product_name | category | price | rating |
|---|---|---|---|---|
| 1 | MacBook Pro | Electronics | 1999.99 | 4.8 |
| 2 | iPad Air | Electronics | 599.99 | 4.8 |
| 3 | AirPods Pro | Electronics | 249.99 | 4.7 |
reservations 5 rows
- reservation_id integer PK
- room_id integer
- guest_name text
- check_in text
- check_out text
| reservation_id | room_id | guest_name | check_in | check_out |
|---|---|---|---|---|
| 1 | 101 | Alice | 2025-01-10 | 2025-01-15 |
| 2 | 101 | Bob | 2025-01-13 | 2025-01-18 |
| 3 | 101 | Carol | 2025-01-20 | 2025-01-25 |
stock_prices 10 rows
- price_date text PK
- closing_price real
| price_date | closing_price |
|---|---|
| 2025-01-01 | 150.0 |
| 2025-01-02 | 152.5 |
| 2025-01-03 | 148.75 |
subscriptions 5 rows
- user_id integer PK
- username text
- subscription_start text
| user_id | username | subscription_start |
|---|---|---|
| 1 | alice_tech | 2024-01-15 |
| 2 | bob_dev | 2024-06-20 |
| 3 | carol_data | 2024-11-10 |
user_activity 18 rows
- activity_id integer PK
- user_id integer
- activity_date text
- activity_type text
| activity_id | user_id | activity_date | activity_type |
|---|---|---|---|
| 1 | 1 | 2025-01-01 | login |
| 2 | 1 | 2025-01-02 | login |
| 3 | 1 | 2025-01-03 | login |
user_cohorts 10 rows
- user_id integer PK
- signup_month text
- active_month text
| user_id | signup_month | active_month |
|---|---|---|
| 1 | 2025-01 | 2025-01 |
| 2 | 2025-01 | 2025-01 |
| 3 | 2025-01 | 2025-02 |
user_events 13 rows
- event_id integer PK
- user_id integer
- event_time text
- event_type text
| event_id | user_id | event_time | event_type |
|---|---|---|---|
| 1 | 1 | 2025-01-05 10:00:00 | page_view |
| 2 | 1 | 2025-01-05 10:05:00 | click |
| 3 | 1 | 2025-01-05 10:15:00 | page_view |
yearly_revenue 5 rows
- year integer PK
- revenue real
| year | revenue |
|---|---|
| 2020 | 1000000.0 |
| 2021 | 1200000.0 |
| 2022 | 1500000.0 |
How the tables connect
- order_items.product_idproducts.product_id
- order_items.order_idorders.order_id
- orders.customer_idcustomers.customer_id
Try a query
Use it on your computer
The SQLite file opens with the sqlite3 command-line tool or any SQLite app:
sqlite3 ecommerce.sqlite
.tables
For MySQL, PostgreSQL or SQL Server, run the SQL script instead. It is plain CREATE TABLE and INSERT statements
written for SQLite, so another database may need a small change.
51 practice questions on this database
- Easy Find Duplicate Emails Find Duplicate Email Addresses Free
- Easy Combine Two Tables With UNION Merge Active and Inactive Users Free
- Easy Search by Name Pattern With LIKE Search Products by Name Pattern Free
- Easy Days Between Two Dates Calculate Account Age in Days Free
- Easy Average Order Value per Customer Average Order Value by Customer Free
- Easy Total Revenue per Customer Customer Lifetime Value Free
- Easy Extract the Email Domain Email Domain Analysis Free
- Easy Count Orders by Status Order Status Distribution Free
- Easy Filter a Price Range With BETWEEN Mid-Range Product Filter Free
- Easy List Unique Values per Category List Unique Product Categories Free
- Easy INSERT a New Row Stock a New Product Free
- Easy Create an Index Speed Up Customer Lookups Free
- Easy UNION vs UNION ALL Build a Mailing List With UNION Free
- Medium Nth Purchase per Customer Find Customer's Third Purchase Premium
- Medium Longest Consecutive Login Streak Daily Active User Streaks Premium
- Medium Running Total Daily Running Total of Sales Premium
- Medium RANK vs DENSE_RANK Product Rankings with Tie Handling Premium
- Medium Compare to the Previous Row With LAG Compare Sales to Previous Day Premium
- Medium Find Missing IDs in a Sequence Detect Missing Order IDs Premium
- Medium Pivot Rows Into Columns Pivot Sales by Month Premium
- Medium First and Last Order per Customer Customer Order Bookends Premium
- Medium Customers With No Orders Inactive Customers Report Premium
- Medium 7-Day Moving Average 7-Day Moving Average of Sales Premium
- Medium Products Frequently Bought Together Frequently Bought Together Premium
- Medium Select Every Other Row Select Every Other Row Premium
- Medium Best Seller per Category Best Seller in Each Category Premium
- Medium EXISTS vs IN Find Customers With Recent Orders Free
- Medium Join Four Tables Complete Order Detail Report Premium
- Medium Skip Duplicates With INSERT OR IGNORE Idempotent Customer Import Free
- Medium Copy Rows With INSERT ... SELECT Archive the Churned User Free
- Medium Transfer in a Transaction The Atomic Stock Transfer Free
- Medium Two Changes in One Transaction Cancel the Order, Cleanly Premium
- Medium Percentage of Total Each Category's Share of Revenue Free
- Medium Group By Month Monthly Orders and Revenue Free
- Medium Running Total by Group Running Total per Customer Free
- Medium LEFT JOIN vs INNER JOIN Product Sales per Category, Unsold Included Free
- Hard Monthly User Retention Rate Calculate Monthly Retention Rate Premium
- Hard Year-over-Year Revenue Growth Year-Over-Year Revenue Growth Analysis Premium
- Hard Unique Categories per Customer per Month Cross-Category Shopping Behavior Premium
- Hard Find Overlapping Date Ranges Detect Overlapping Room Bookings Premium
- Hard Longest Winning Streak Longest Winning Streak in Games Premium
- Hard Compound Annual Growth Rate (CAGR) Calculate CAGR (Compound Annual Growth Rate) Premium
- Hard User Session Duration Calculate User Session Duration Premium
- Hard Inventory Reorder Point Calculate Reorder Point for Inventory Premium
- Hard Fuzzy Name Matching Fuzzy Product Name Matching Premium
- Hard Cohort Retention Analysis Monthly Signup Cohort Retention Premium
- Hard Consecutive Increases (Gaps and Islands) Detect Consecutive Price Increases Premium
- Hard Business Metrics in One Query Multi-Metric Executive Dashboard Premium
- Hard UPDATE From Another Table Rebuild the Order Totals Premium
- Hard Partial Index Index Only What You Query Premium
- Hard Upsert With INSERT ... ON CONFLICT Upsert the Stock Count Premium
Practice on your iPhone
Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.