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_idnameemail
1Alice[email protected]
2Bob[email protected]
3Charlie[email protected]
category_revenue 6 rows
  • id integer PK
  • category text
  • year integer
  • total_revenue real
idcategoryyeartotal_revenue
1Electronics2023500000.0
2Electronics2024600000.0
3Furniture2023300000.0
customers 12 rows
  • customer_id integer PK
  • name text
  • email text
  • city text
  • signup_date text
customer_idnameemailcitysignup_date
101Alice Mitchell[email protected]San Francisco2024-01-15
102Bob Chen[email protected]New York2024-02-20
103Carol Davis[email protected]Los Angeles2024-03-10
daily_sales 15 rows
  • sale_date text PK
  • daily_revenue real
sale_datedaily_revenue
2025-01-011000.0
2025-01-021200.0
2025-01-03900.0
game_results 15 rows
  • game_id integer PK
  • player_id integer
  • game_date text
  • result text
game_idplayer_idgame_dateresult
112025-01-01win
212025-01-02win
312025-01-03win
inactive_users 2 rows
  • user_id integer PK
  • name text
  • email text
user_idnameemail
4Carol[email protected]
5Dave[email protected]
missing_orders 7 rows
  • order_id integer PK
  • customer_name text
  • order_date text
order_idcustomer_nameorder_date
1Alice2025-01-01
2Bob2025-01-02
4Carol2025-01-04
monthly_product_revenue 9 rows
  • id integer PK
  • product_name text
  • month text
  • revenue real
idproduct_namemonthrevenue
1MacBook ProJan25000.0
2MacBook ProFeb30000.0
3MacBook ProMar28000.0
monthly_sales 9 rows
  • id integer PK
  • product_name text
  • sale_month text
  • revenue real
idproduct_namesale_monthrevenue
1MacBook Pro2025-0125000.0
2MacBook Pro2025-0230000.0
3MacBook Pro2025-0328000.0
order_items 19 rows
  • id integer PK
  • order_id integer → orders
  • product_id integer → products
  • quantity integer
idorder_idproduct_idquantity
1131
2211
3261
orders 15 rows
  • order_id integer PK
  • customer_id integer → customers
  • order_date text
  • total_amount real
  • status text
order_idcustomer_idorder_datetotal_amountstatus
11012024-01-20249.99delivered
21012024-03-151999.99delivered
41012024-09-0579.99delivered
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_idproduct_namecurrent_stockavg_daily_saleslead_time_dayssafety_stock
1Widget A50025.0750
2Widget B30040.010100
3Widget C20015.0530
products 20 rows
  • product_id integer PK
  • product_name text
  • category text
  • price real
  • rating real
product_idproduct_namecategorypricerating
1MacBook ProElectronics1999.994.8
2iPad AirElectronics599.994.8
3AirPods ProElectronics249.994.7
reservations 5 rows
  • reservation_id integer PK
  • room_id integer
  • guest_name text
  • check_in text
  • check_out text
reservation_idroom_idguest_namecheck_incheck_out
1101Alice2025-01-102025-01-15
2101Bob2025-01-132025-01-18
3101Carol2025-01-202025-01-25
stock_prices 10 rows
  • price_date text PK
  • closing_price real
price_dateclosing_price
2025-01-01150.0
2025-01-02152.5
2025-01-03148.75
subscriptions 5 rows
  • user_id integer PK
  • username text
  • subscription_start text
user_idusernamesubscription_start
1alice_tech2024-01-15
2bob_dev2024-06-20
3carol_data2024-11-10
user_activity 18 rows
  • activity_id integer PK
  • user_id integer
  • activity_date text
  • activity_type text
activity_iduser_idactivity_dateactivity_type
112025-01-01login
212025-01-02login
312025-01-03login
user_cohorts 10 rows
  • user_id integer PK
  • signup_month text
  • active_month text
user_idsignup_monthactive_month
12025-012025-01
22025-012025-01
32025-012025-02
user_events 13 rows
  • event_id integer PK
  • user_id integer
  • event_time text
  • event_type text
event_iduser_idevent_timeevent_type
112025-01-05 10:00:00page_view
212025-01-05 10:05:00click
312025-01-05 10:15:00page_view
yearly_revenue 5 rows
  • year integer PK
  • revenue real
yearrevenue
20201000000.0
20211200000.0
20221500000.0

How the tables connect

Try a query

SELECT c.name, COUNT(o.order_id) AS orders, ROUND(SUM(o.total_amount), 2) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY spent DESC;

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

  1. Easy Find Duplicate Emails Find Duplicate Email Addresses Free
  2. Easy Combine Two Tables With UNION Merge Active and Inactive Users Free
  3. Easy Search by Name Pattern With LIKE Search Products by Name Pattern Free
  4. Easy Days Between Two Dates Calculate Account Age in Days Free
  5. Easy Average Order Value per Customer Average Order Value by Customer Free
  6. Easy Total Revenue per Customer Customer Lifetime Value Free
  7. Easy Extract the Email Domain Email Domain Analysis Free
  8. Easy Count Orders by Status Order Status Distribution Free
  9. Easy Filter a Price Range With BETWEEN Mid-Range Product Filter Free
  10. Easy List Unique Values per Category List Unique Product Categories Free
  11. Easy INSERT a New Row Stock a New Product Free
  12. Easy Create an Index Speed Up Customer Lookups Free
  13. Easy UNION vs UNION ALL Build a Mailing List With UNION Free
  14. Medium Nth Purchase per Customer Find Customer's Third Purchase Premium
  15. Medium Longest Consecutive Login Streak Daily Active User Streaks Premium
  16. Medium Running Total Daily Running Total of Sales Premium
  17. Medium RANK vs DENSE_RANK Product Rankings with Tie Handling Premium
  18. Medium Compare to the Previous Row With LAG Compare Sales to Previous Day Premium
  19. Medium Find Missing IDs in a Sequence Detect Missing Order IDs Premium
  20. Medium Pivot Rows Into Columns Pivot Sales by Month Premium
  21. Medium First and Last Order per Customer Customer Order Bookends Premium
  22. Medium Customers With No Orders Inactive Customers Report Premium
  23. Medium 7-Day Moving Average 7-Day Moving Average of Sales Premium
  24. Medium Products Frequently Bought Together Frequently Bought Together Premium
  25. Medium Select Every Other Row Select Every Other Row Premium
  26. Medium Best Seller per Category Best Seller in Each Category Premium
  27. Medium EXISTS vs IN Find Customers With Recent Orders Free
  28. Medium Join Four Tables Complete Order Detail Report Premium
  29. Medium Skip Duplicates With INSERT OR IGNORE Idempotent Customer Import Free
  30. Medium Copy Rows With INSERT ... SELECT Archive the Churned User Free
  31. Medium Transfer in a Transaction The Atomic Stock Transfer Free
  32. Medium Two Changes in One Transaction Cancel the Order, Cleanly Premium
  33. Medium Percentage of Total Each Category's Share of Revenue Free
  34. Medium Group By Month Monthly Orders and Revenue Free
  35. Medium Running Total by Group Running Total per Customer Free
  36. Medium LEFT JOIN vs INNER JOIN Product Sales per Category, Unsold Included Free
  37. Hard Monthly User Retention Rate Calculate Monthly Retention Rate Premium
  38. Hard Year-over-Year Revenue Growth Year-Over-Year Revenue Growth Analysis Premium
  39. Hard Unique Categories per Customer per Month Cross-Category Shopping Behavior Premium
  40. Hard Find Overlapping Date Ranges Detect Overlapping Room Bookings Premium
  41. Hard Longest Winning Streak Longest Winning Streak in Games Premium
  42. Hard Compound Annual Growth Rate (CAGR) Calculate CAGR (Compound Annual Growth Rate) Premium
  43. Hard User Session Duration Calculate User Session Duration Premium
  44. Hard Inventory Reorder Point Calculate Reorder Point for Inventory Premium
  45. Hard Fuzzy Name Matching Fuzzy Product Name Matching Premium
  46. Hard Cohort Retention Analysis Monthly Signup Cohort Retention Premium
  47. Hard Consecutive Increases (Gaps and Islands) Detect Consecutive Price Increases Premium
  48. Hard Business Metrics in One Query Multi-Metric Executive Dashboard Premium
  49. Hard UPDATE From Another Table Rebuild the Order Totals Premium
  50. Hard Partial Index Index Only What You Query Premium
  51. 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.

Get the app

More sample databases