SQL Tables
Your Course Tables
The nine tables you will use throughout this SQL for Product Growth course — a complete SaaS product database covering user acquisition, session behaviour, in-app events, orders, payments, and A/B experiment results.
What These Tables Are For
- Query a realistic SaaS product database to answer the core questions a product growth analyst faces daily: Who signed up, from which channel, on which device — and are they still active?
- Track user behaviour at three levels of granularity — session (when and how long), event (what they did within a session), and order (what they purchased) — and join these layers to build complete user journeys
- Analyse the subscription revenue model: which plan drives the most upgrades, which users hold add-ons, and how does paid conversion differ by acquisition channel and country
- Evaluate A/B experiments using SQL: compare conversion_flag rates and revenue_generated between variant A and variant B — the fundamental growth experiment query used in product analytics teams
The users table is the master record for every person who has signed up for the product. Eight users span four countries (Canada, USA, UK, Australia), three device types, four acquisition channels, two plan types, and four lifecycle statuses. User 3 (Noah Patel, USA) is the only Churned user. User 7 (Sophia Brown, Australia) is Inactive. The remaining six are Active — four on Free plans and four on Premium.
User 3 (Noah Patel) has user_status = Churned — they signed up on a Free plan via Referral and left. Their session (1003) lasted only 12 minutes; their events show only a login and a pricing page view with no upgrade action. User 7 (Sophia Brown) is Inactive — also on a Free Referral plan, with the shortest session in the dataset (10 minutes, 7 pages). Filtering WHERE user_status IN (‘Churned’,’Inactive’) and joining to sessions and events reveals the behavioural patterns that precede disengagement — a core retention analysis in the course.
| user_id | full_name | signup_date | country | device_type | acquisition_channel | plan_type | user_status |
|---|---|---|---|---|---|---|---|
| 1 | Ava Thompson | 2024-01-05 | Canada | Mobile | Organic Search | Free | Active |
| 2 | Liam Chen | 2024-01-08 | Canada | Desktop | Paid Ads | Premium | Active |
| 3 | Noah Patel | 2024-01-15 | USA | Mobile | Referral | Free | Churned |
| 4 | Emma Rodriguez | 2024-02-01 | UK | Tablet | Organic Search | Premium | Active |
| 5 | Olivia Martin | 2024-02-10 | Canada | Desktop | Email Campaign | Free | Active |
| 6 | Ethan Wilson | 2024-02-15 | USA | Mobile | Paid Ads | Premium | Active |
| 7 | Sophia Brown | 2024-03-02 | Australia | Desktop | Referral | Free | Inactive |
| 8 | Mason Lee | 2024-03-10 | Canada | Mobile | Organic Search | Premium | Active |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| user_id | INT | PRIMARY KEY | Unique identifier (1–8). The central key for the entire database — every other table links back to users via user_id. |
| full_name | VARCHAR(100) | None | User’s full name. Used in report output and user-level queries. |
| signup_date | DATE | None | Date of account creation. Ranges from 2024-01-05 to 2024-03-10. Used to calculate days since signup and cohort groupings. |
| country | VARCHAR(50) | None | Canada (4), USA (2), UK (1), Australia (1). Used to segment conversion and churn rates by geography. |
| device_type | VARCHAR(30) | None | Mobile (4), Desktop (3), Tablet (1). The device used at signup — compare to session device_type for consistency checks. |
| acquisition_channel | VARCHAR(50) | None | Organic Search (3), Paid Ads (2), Referral (2), Email Campaign (1). The channel that drove the user to sign up. |
| plan_type | VARCHAR(20) | None | Free (4 — users 1, 3, 5, 7) or Premium (4 — users 2, 4, 6, 8). Used to calculate free-to-paid conversion rate. |
| user_status | VARCHAR(20) | None | Active (6), Churned (1 — user 3), Inactive (1 — user 7). The current lifecycle state of the user. |
The products table defines the five purchasable items in the product catalogue. Three are subscription plans: Starter Plan (free, $0.00), Pro Plan ($29.99/month), and Team Plan ($79.99/month). Two are add-ons: Analytics Add-On ($14.99) and Growth Toolkit ($19.99). All five products are currently active (is_active = TRUE). Product IDs link to order_items, tying purchases to specific plan or add-on choices.
The Subscription category covers the core plan tier a user is on. The Add-On category covers optional extras purchased on top of a plan. In the order_items data, user 8 (Mason Lee) purchased both a Pro Plan (product 102) and the Analytics Add-On (product 104) in the same order — a bundle pattern common in SaaS upsell analysis. Grouping order_items by category and summing line_total breaks revenue into plan revenue vs add-on revenue.
| product_id | product_name | category | monthly_price | is_active |
|---|---|---|---|---|
| 101 | Starter Plan | Subscription | $0.00 | TRUE |
| 102 | Pro Plan | Subscription | $29.99 | TRUE |
| 103 | Team Plan | Subscription | $79.99 | TRUE |
| 104 | Analytics Add-On | Add-On | $14.99 | TRUE |
| 105 | Growth Toolkit | Add-On | $19.99 | TRUE |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| product_id | INT | PRIMARY KEY | Unique identifier (101–105). Referenced by order_items as a foreign key. |
| product_name | VARCHAR(100) | None | Starter Plan, Pro Plan, Team Plan, Analytics Add-On, Growth Toolkit. |
| category | VARCHAR(50) | None | Subscription (3 — products 101–103) or Add-On (2 — products 104–105). |
| monthly_price | DECIMAL(10,2) | None | Monthly recurring price. $0.00 (Starter), $14.99 (Analytics Add-On), $19.99 (Growth Toolkit), $29.99 (Pro), $79.99 (Team). |
| is_active | BOOLEAN | None | TRUE for all 5 products — no deprecated plans in the dataset. Filter WHERE is_active = TRUE in queries to exclude retired products in future analyses. |
The sessions table records each user’s product session during the April 2024 observation window. One session per user in this dataset. Session IDs (1001–1008) link directly to events — every in-app event is tagged with the session_id in which it occurred. Session duration ranges from 10 minutes (user 7, Sophia Brown) to 40 minutes (user 6, Ethan Wilson). Longer sessions tend to contain more diverse event types and conversion actions.
Session duration is a leading indicator of engagement quality. User 2 (Liam Chen, 35 minutes) and user 4 (Emma Rodriguez, 35 minutes) both have longer sessions and both converted — user 2 triggering an upgrade_plan event, user 4 using the Cohort Analysis feature. Users 3 and 7 (12 and 10 minutes respectively) are the two disengaged users — short sessions with low event counts. Joining sessions to events and grouping by session_duration_minutes shows the relationship between time-on-product and conversion likelihood.
| session_id | user_id | session_start | session_end | duration (min) | traffic_source | device_type |
|---|---|---|---|---|---|---|
| 1001 | 1 — Ava Thompson | 2024-04-01 09:00 | 2024-04-01 09:18 | 18 | Organic Search | Mobile |
| 1002 | 2 — Liam Chen | 2024-04-01 10:15 | 2024-04-01 10:50 | 35 | Paid Ads | Desktop |
| 1003 | 3 — Noah Patel | 2024-04-02 11:00 | 2024-04-02 11:12 | 12 | Referral | Mobile |
| 1004 | 4 — Emma Rodriguez | 2024-04-02 13:30 | 2024-04-02 14:05 | 35 | Organic Search | Tablet |
| 1005 | 5 — Olivia Martin | 2024-04-03 08:45 | 2024-04-03 09:00 | 15 | Email Campaign | Desktop |
| 1006 | 6 — Ethan Wilson | 2024-04-03 16:00 | 2024-04-03 16:40 | 40 | Paid Ads | Mobile |
| 1007 | 7 — Sophia Brown | 2024-04-04 12:10 | 2024-04-04 12:20 | 10 | Referral | Desktop |
| 1008 | 8 — Mason Lee | 2024-04-04 17:00 | 2024-04-04 17:28 | 28 | Organic Search | Mobile |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| session_id | INT | PRIMARY KEY | Unique identifier (1001–1008). Referenced by events as a foreign key. |
| user_id | INT | FOREIGN KEY → users | The user this session belongs to. One session per user in this dataset. |
| session_start / session_end | DATETIME | None | Exact timestamps of the session. Use TIMEDIFF or TIMESTAMPDIFF to compute duration from these columns and verify against session_duration_minutes. |
| session_duration_minutes | INT | None | Pre-calculated session length. Ranges from 10 (user 7) to 40 (user 6). Used for engagement benchmarking. |
| traffic_source | VARCHAR(50) | None | Organic Search (3), Paid Ads (2), Referral (2), Email Campaign (1). The channel that drove this session — matches acquisition_channel in users for returning users. |
| device_type | VARCHAR(30) | None | Mobile (4), Desktop (3), Tablet (1). The device used during the session. |
The events table is the most granular table in the database — it records every individual user action within a session. Fifteen events span 7 of the 8 users (user 7, Sophia Brown, has no events in the dataset — session 1007 produced no event records). Event types include: login (5), view_dashboard (2), start_trial (1), use_feature (3), upgrade_plan (1), view_pricing (1), complete_checkout (1), and use_feature (1 — Funnel Explorer). Events with NULL feature_name are page-level events with no specific feature interaction.
The events table contains the full conversion funnel sequence: login → view_dashboard → start_trial (user 1, session 1001) and login → use_feature → upgrade_plan (user 2, session 1002) and login → complete_checkout (user 6, session 1006). User 3 (Churned) goes login → view_pricing but never proceeds — a classic drop-off pattern. Querying event sequences with ORDER BY event_timestamp within a session_id reveals exactly where users abandon the funnel.
| event_id | user_id | session_id | event_name | event_timestamp | page_name | feature_name |
|---|---|---|---|---|---|---|
| 2001 | 1 | 1001 | login | 2024-04-01 09:01 | Login Page | NULL |
| 2002 | 1 | 1001 | view_dashboard | 2024-04-01 09:03 | Dashboard | Dashboard |
| 2003 | 1 | 1001 | start_trial | 2024-04-01 09:10 | Pricing | Trial |
| 2004 | 2 | 1002 | login | 2024-04-01 10:16 | Login Page | NULL |
| 2005 | 2 | 1002 | use_feature | 2024-04-01 10:25 | Analytics | Report Builder |
| 2006 | 2 | 1002 | upgrade_plan | 2024-04-01 10:40 | Billing | Pro Upgrade |
| 2007 | 3 | 1003 | login | 2024-04-02 11:01 | Login Page | NULL |
| 2008 | 3 | 1003 | view_pricing | 2024-04-02 11:08 | Pricing | NULL |
| 2009 | 4 | 1004 | login | 2024-04-02 13:32 | Login Page | NULL |
| 2010 | 4 | 1004 | use_feature | 2024-04-02 13:45 | Analytics | Cohort Analysis |
| 2011 | 5 | 1005 | login | 2024-04-03 08:46 | Login Page | NULL |
| 2012 | 5 | 1005 | view_dashboard | 2024-04-03 08:50 | Dashboard | Dashboard |
| 2013 | 6 | 1006 | login | 2024-04-03 16:01 | Login Page | NULL |
| 2014 | 6 | 1006 | complete_checkout | 2024-04-03 16:30 | Checkout | Subscription Purchase |
| 2015 | 8 | 1008 | use_feature | 2024-04-04 17:10 | Growth Tools | Funnel Explorer |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| event_id | INT | PRIMARY KEY | Unique identifier (2001–2015). The 2000-series prefix distinguishes events from sessions (1000s). |
| user_id | INT | FOREIGN KEY → users | The user who triggered the event. |
| session_id | INT | FOREIGN KEY → sessions | The session during which this event occurred. Use to group events by session and reconstruct user journeys. |
| event_name | VARCHAR(50) | None | login (5), use_feature (3), view_dashboard (2), start_trial/upgrade_plan/view_pricing/complete_checkout/use_feature (1 each). The action taken. |
| event_timestamp | DATETIME | None | Exact time the event fired. Order by event_timestamp within a session to reconstruct click sequences. |
| page_name | VARCHAR(50) | None | The page the user was on when the event fired. Login Page, Dashboard, Pricing, Analytics, Billing, Checkout, Growth Tools. |
| feature_name | VARCHAR(50) | None | The specific feature engaged with. NULL for page-level events (login, view_pricing). Non-NULL for feature interactions: Report Builder, Cohort Analysis, Funnel Explorer, Dashboard, Trial, Pro Upgrade, Subscription Purchase. |
The orders table records the five subscription purchases made during the observation window. Orders link to users via user_id — notably, all five orders are placed by users who are either already Premium or converting to Premium during this period. Order 3005 (user 5, Olivia Martin) is Pending — a Free user whose Growth Toolkit add-on purchase has not yet been completed. Order 3004 (user 8, Mason Lee) is the highest-value at $49.98 — a two-item bundle.
Order 3005 (Olivia Martin, $19.99, Pending) is the only incomplete transaction in the dataset. Her corresponding payment (5005) is also Pending via Bank Transfer. Joining orders to users WHERE plan_type = ‘Free’ and order_status = ‘Pending’ identifies Free users who have initiated a purchase but not completed it — a high-priority segment for intervention in a product growth context, since they are in the conversion funnel but have not yet crossed it.
| order_id | user_id | order_date | order_status | total_amount |
|---|---|---|---|---|
| 3001 | 2 — Liam Chen | 2024-04-01 | Completed | $29.99 |
| 3002 | 4 — Emma Rodriguez | 2024-04-02 | Completed | $79.99 |
| 3003 | 6 — Ethan Wilson | 2024-04-03 | Completed | $29.99 |
| 3004 | 8 — Mason Lee | 2024-04-04 | Completed | $49.98 |
| 3005 | 5 — Olivia Martin | 2024-04-05 | Pending | $19.99 |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| order_id | INT | PRIMARY KEY | Unique identifier (3001–3005). The 3000-series prefix distinguishes orders from sessions (1000s) and events (2000s). Referenced by order_items and payments. |
| user_id | INT | FOREIGN KEY → users | The user who placed the order. Users 3 and 7 (Churned/Inactive) have no orders — only Active/converting users appear here. |
| order_date | DATE | None | Date the order was placed. All five orders fall within April 1–5, 2024 — the core observation window. |
| order_status | VARCHAR(30) | None | Completed (4 — orders 3001–3004) or Pending (1 — order 3005). |
| total_amount | DECIMAL(10,2) | None | Order total. Ranges from $19.99 (Growth Toolkit only) to $79.99 (Team Plan). Order 3004 = $49.98 — a two-product bundle. |
The order_items table records each product line within an order. Six items span five orders. Orders 3001, 3002, 3003, and 3005 each contain a single line item. Order 3004 (Mason Lee) contains two line items — a Pro Plan ($29.99) plus an Analytics Add-On ($19.99) — totalling $49.98. Joining order_items to products on product_id reveals which plans and add-ons are being purchased and at what volume.
Of the six order line items, four are subscription plans (products 102 and 103) and two are add-ons (products 104 and 105). Subscription revenue: $29.99 + $79.99 + $29.99 + $29.99 = $169.96. Add-on revenue: $19.99 + $19.99 = $39.98. Grouping order_items by products.category and summing line_total gives the plan-vs-add-on revenue breakdown — a fundamental SaaS monetisation query.
| order_item_id | order_id | product_id | quantity | unit_price | line_total |
|---|---|---|---|---|---|
| 4001 | 3001 | 102 — Pro Plan | 1 | $29.99 | $29.99 |
| 4002 | 3002 | 103 — Team Plan | 1 | $79.99 | $79.99 |
| 4003 | 3003 | 102 — Pro Plan | 1 | $29.99 | $29.99 |
| 4004 | 3004 | 102 — Pro Plan | 1 | $29.99 | $29.99 |
| 4005 | 3004 | 104 — Analytics Add-On | 1 | $19.99 | $19.99 |
| 4006 | 3005 | 105 — Growth Toolkit | 1 | $19.99 | $19.99 |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| order_item_id | INT | PRIMARY KEY | Unique identifier (4001–4006). The 4000-series distinguishes line items from orders (3000s). |
| order_id | INT | FOREIGN KEY → orders | The parent order. Order 3004 has two items (4004 and 4005); all others have one. |
| product_id | INT | FOREIGN KEY → products | The product purchased. Pro Plan (102) appears 3 times — the most popular paid plan. |
| quantity | INT | None | All quantities are 1 — subscription products are per-seat, not multi-unit in this dataset. |
| unit_price | DECIMAL(10,2) | None | Price charged at time of purchase — matches products.monthly_price for all items. |
| line_total | DECIMAL(10,2) | None | unit_price × quantity. Since all quantities are 1, line_total equals unit_price throughout. |
The payments table records the payment made against each order — one payment per order. Four payments are Paid (credit card and PayPal). Payment 5005 (Bank Transfer, order 3005, user 5 Olivia Martin) is Pending — matching the Pending order status. Total confirmed revenue from Paid payments: $29.99 + $79.99 + $29.99 + $49.98 = $189.95.
Credit Card (payments 5001, 5003, 5004) accounts for $109.97 in confirmed revenue — the dominant payment method. PayPal (payment 5002) covers the highest single transaction at $79.99 (the Team Plan). Bank Transfer (payment 5005) is Pending — it has not yet cleared. Grouping payments by payment_method and summing amount_paid WHERE payment_status = ‘Paid’ gives total collected revenue by channel — a standard finance and growth reporting query.
| payment_id | order_id | payment_date | payment_method | payment_status | amount_paid |
|---|---|---|---|---|---|
| 5001 | 3001 | 2024-04-01 | Credit Card | Paid | $29.99 |
| 5002 | 3002 | 2024-04-02 | PayPal | Paid | $79.99 |
| 5003 | 3003 | 2024-04-03 | Credit Card | Paid | $29.99 |
| 5004 | 3004 | 2024-04-04 | Credit Card | Paid | $49.98 |
| 5005 | 3005 | 2024-04-05 | Bank Transfer | Pending | $19.99 |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| payment_id | INT | PRIMARY KEY | Unique identifier (5001–5005). The 5000-series distinguishes payments from order_items (4000s). |
| order_id | INT | FOREIGN KEY → orders | The order this payment settles. One-to-one with orders in this dataset. |
| payment_date | DATE | None | All payments dated the same as their order — same-day processing for completed payments. |
| payment_method | VARCHAR(30) | None | Credit Card (3), PayPal (1), Bank Transfer (1 — Pending). |
| payment_status | VARCHAR(30) | None | Paid (4) or Pending (1 — payment 5005). Filter WHERE payment_status = ‘Paid’ for confirmed revenue calculations. |
| amount_paid | DECIMAL(10,2) | None | Payment value — matches orders.total_amount for each order. |
The experiments table defines the two A/B tests running during the observation period. Experiment 6001 (New Pricing Page Test) ran April 1–15 and is Completed — it measured Conversion Rate. Experiment 6002 (Onboarding Email Test) started April 5 and is still Running — it measures Retention Rate. Each experiment links to multiple user-level results in experiment_results via experiment_id.
Experiment 6001 targets the top of the conversion funnel — does a new pricing page design drive more users to purchase? Experiment 6002 targets post-signup retention — does a different onboarding email sequence keep users active longer? Together they represent the two most common growth experiment types: acquisition-side optimisation and retention-side optimisation. Joining experiments to experiment_results lets you compare variant A vs variant B conversion rates for each.
| experiment_id | experiment_name | start_date | end_date | target_metric | experiment_status |
|---|---|---|---|---|---|
| 6001 | New Pricing Page Test | 2024-04-01 | 2024-04-15 | Conversion Rate | Completed |
| 6002 | Onboarding Email Test | 2024-04-05 | 2024-04-20 | Retention Rate | Running |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| experiment_id | INT | PRIMARY KEY | Unique identifier (6001–6002). Referenced by experiment_results as a foreign key. |
| experiment_name | VARCHAR(100) | None | Descriptive name. New Pricing Page Test and Onboarding Email Test. |
| start_date / end_date | DATE | None | Experiment duration. Experiment 6001 ran 14 days; experiment 6002 runs 15 days. Use DATEDIFF to calculate duration. |
| target_metric | VARCHAR(50) | None | Conversion Rate (experiment 6001) or Retention Rate (experiment 6002). Defines what success looks like for each test. |
| experiment_status | VARCHAR(30) | None | Completed (1 — experiment 6001) or Running (1 — experiment 6002). Filter WHERE experiment_status = ‘Completed’ before drawing conclusions. |
The experiment_results table records one row per user per experiment — the variant they were assigned (A or B), whether they converted (conversion_flag = TRUE/FALSE), and the revenue they generated. For experiment 6001 (Pricing Page Test): users 2, 4, and 6 were assigned Variant B and all converted; users 1 and 5 were assigned Variant A and did not convert. For experiment 6002 (Onboarding Email): user 7 (A, no conversion) vs user 8 (B, converted, $49.98).
In experiment 6001, Variant B (the new pricing page) achieved a 3/3 (100%) conversion rate with $139.97 in revenue generated. Variant A achieved 0/2 (0%) conversion and $0 revenue. While the sample size is small (5 users), the directional signal is clear: the new pricing page drove all conversions. The core query — SELECT variant, COUNT(*) as users, SUM(conversion_flag) as conversions, SUM(revenue_generated) as revenue FROM experiment_results WHERE experiment_id = 6001 GROUP BY variant — produces the A/B summary table used in product growth reporting.
| result_id | experiment_id | user_id | variant | conversion_flag | revenue_generated |
|---|---|---|---|---|---|
| 7001 | 6001 | 1 — Ava Thompson | A | FALSE | $0.00 |
| 7002 | 6001 | 2 — Liam Chen | B | TRUE | $29.99 |
| 7003 | 6001 | 4 — Emma Rodriguez | B | TRUE | $79.99 |
| 7004 | 6001 | 5 — Olivia Martin | A | FALSE | $0.00 |
| 7005 | 6001 | 6 — Ethan Wilson | B | TRUE | $29.99 |
| 7006 | 6002 | 7 — Sophia Brown | A | FALSE | $0.00 |
| 7007 | 6002 | 8 — Mason Lee | B | TRUE | $49.98 |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| result_id | INT | PRIMARY KEY | Unique identifier (7001–7007). The 7000-series distinguishes results from experiments (6000s). |
| experiment_id | INT | FOREIGN KEY → experiments | Which experiment this result belongs to. 5 results for experiment 6001; 2 for experiment 6002. |
| user_id | INT | FOREIGN KEY → users | The user who participated. Joining to users adds country, device, acquisition_channel for subgroup analysis. |
| variant | VARCHAR(10) | None | A (control — 3 users) or B (treatment — 4 users). Variant B is the new experience being tested. |
| conversion_flag | BOOLEAN | None | TRUE (4 — all Variant B users) or FALSE (3 — all Variant A users). In experiment 6001, Variant B = 100% conversion; Variant A = 0%. |
| revenue_generated | DECIMAL(10,2) | None | Revenue attributed to the user during the experiment window. $0.00 for non-converters; $29.99–$79.99 for converters. SUM by variant gives total revenue impact of each version. |
Quick Reference — All 9 Tables
The complete product_growth_sql_course database at a glance.
8 rows — Free/Premium, 4 countries, 4 channels. PK: user_id (1–8). Users 3 (Churned) and 7 (Inactive) flagged.
5 rows — 3 plans, 2 add-ons, all active. PK: product_id (101–105). Pro Plan = most purchased.
8 rows — 1 per user, 10–40 min duration. PK: session_id (1001–1008). Sessions 1003 and 1007 = shortest (disengaged users).
15 rows — 7 event types, NULL feature_name for page events. PK: event_id (2001–2015). User 7 has no events.
5 rows — 4 Completed, 1 Pending. PK: order_id (3001–3005). Order 3004 = highest value ($49.98 bundle).
6 rows — 4 plan lines, 2 add-on lines. PK: order_item_id (4001–4006). Order 3004 = only multi-line order.
5 rows — 4 Paid, 1 Pending. PK: payment_id (5001–5005). Confirmed revenue = $189.95.
2 rows — 1 Completed, 1 Running. PK: experiment_id (6001–6002). Metrics: Conversion Rate and Retention Rate.
7 rows — Variant A (3 users, 0 conversions) vs Variant B (4 users, 4 conversions). PK: result_id (7001–7007).
