Overview
Overview of SQL for Product & Growth
A practical course for analyzing user behavior, product performance, and business growth — using SQL on real digital product datasets.
Learning Objectives
- Understand how product data is organized across users, events, sessions, orders, and experiments — and how each table connects across the user lifecycle.
- Distinguish between user-level, session-level, event-level, and transaction-level data and explain how each shapes SQL query design.
- Write SQL queries to analyze user engagement, retention, funnel conversion, and revenue using real behavioral product data.
- Apply advanced SQL techniques to perform cohort analysis, A/B testing evaluation, growth trend analysis, and product decision support.
SQL for Product & Growth Analytics follows a structured progression — starting with how digital product data is organized, moving to behavioral and growth analysis, and finishing with advanced product insights that support strategic decisions. The same integrated dataset runs through all three parts.
Understanding product analytics tables — users, events, and sessions. Learning how user activity is captured, how to write basic queries, perform first aggregations, and distinguish new from returning users.
Joining behavioral and transactional data across tables. Retention analysis, funnel and conversion tracking, cohort analysis, feature engagement, customer journey mapping, and A/B testing.
Advanced product analytics — user segmentation, lifecycle analysis, growth trend reporting, conversion optimization, revenue and monetization analysis, experiment evaluation, and product decision support.
By the end of all three parts, learners will be able to answer the questions product and growth teams actually ask — “Where are users dropping off?”, “Which cohorts retain best?”, “Which experiment won?”, “What drives revenue?” — directly from behavioral data using SQL.
Every table in this course reflects how real digital products store data — from user profiles to raw event streams to revenue records and A/B test assignments. The eight tables are organized across three groups that align with the course progression.
User counts, geographic breakdowns, acquisition channel analysis, plan-type distribution, and new vs. returning user identification. Almost every product query starts here.
The backbone of all product analytics. Used for feature usage tracking, funnel analysis, engagement measurement, daily active user counts, and behavioral event sequencing.
Engagement depth analysis, session duration distribution, traffic source attribution, identifying multi-session users, and connecting event data to visit context.
Conversion rate analysis, total revenue, average order value, repeat purchase identification, and linking behavioral engagement to monetary outcomes.
Revenue tracking, payment success rate analysis, payment method breakdown, failed payment identification, and cash flow reporting.
Product-level revenue analysis, identifying top-selling items, average quantity per order, and understanding which products drive the most monetization value.
Feature and product performance analysis, category revenue breakdown, pricing analysis, and joining product metadata to order line items for deeper monetization insights.
A/B test analysis — comparing conversion rates, engagement, and revenue between control and treatment groups to determine which product changes are working.
How the Tables Connect
user_id is the central key that links all behavioral and transactional data back to a specific user. session_id connects events to their visit context, order_id links orders to their line items and payments, and product_id joins order items to the product catalogue.
Before writing any query, always ask: Am I analyzing users, sessions, individual actions, or transactions? The answer determines which table to start from and how every join should be structured.
Stores fixed profile attributes — country, device, plan, acquisition channel. Use for demographic breakdowns, plan distribution, and segmenting users before analyzing their behavior.
Stores each product visit — start time, end time, duration, and traffic source. One user can have many sessions. Use for engagement depth, traffic attribution, and session frequency.
Stores every individual interaction — clicks, page views, feature uses, logins. The most granular level. One session can contain dozens of events. Use for funnel analysis and feature tracking.
Stores each order or payment. One user can make many purchases over time. Use for revenue analysis, conversion rates, and connecting behavioral engagement to monetary outcomes.
The Full Data Hierarchy
| Level | Table(s) | Relationship | Example Product Question |
|---|---|---|---|
| User | users | 1 row per user | “How many users signed up this month by channel?” |
| Session | sessions | Many rows per user | “What is the average session duration per traffic source?” |
| Event | events | Many rows per session | “How many users triggered the checkout_started event?” |
| Order | orders | Many rows per user | “What is the conversion rate from signup to first purchase?” |
| Experiment | experiments | Many rows per user | “Did the treatment group convert at a higher rate than control?” |
Always verify row counts after joining. A user joined to their events will produce one row per event — not one row per user. Use COUNT(DISTINCT user_id) when the question is about people, and COUNT(*) only when the question is about individual actions or events.
Product and growth analytics is built around a set of core fields that appear across nearly every query in the course. Understanding what each field represents — and how it behaves across different data levels — is essential before writing any product SQL.
user_id — The Central Key
Every table in the schema connects back to a user through user_id. It is the thread that allows analysts to stitch together a complete picture of a user’s journey — from their first session to their most recent purchase.
| Field | Table | Why It Matters for Product Analytics |
|---|---|---|
| signup_date | users | Anchors cohort analysis — grouping users by the month or week they joined to track behavior over time. |
| acquisition_channel | users | Identifies how users found the product — paid, organic, referral, social — enabling channel ROI and quality comparison. |
| event_name | events | The specific action a user took — page_view, signup, checkout_started, purchase_complete. Filtering by event_name is how funnels are built. |
| event_timestamp | events | Powers all time-based analysis — daily active users, session frequency, time between events, and behavioral trend analysis. |
| traffic_source | sessions | Identifies where each visit originated — used for session-level attribution and comparing engagement quality across traffic channels. |
| variant | experiments | The A/B test group assigned to a user — Control or Treatment. Filtering by variant and comparing outcomes is the core of experiment analysis. |
| total_amount | orders | The monetary value of a purchase — used for average order value, total revenue, and lifetime value calculations. |
Key Questions These Fields Answer
| Product Question | Primary Field(s) | Table(s) |
|---|---|---|
| “How many daily active users did we have this week?” | event_timestamp · user_id | events |
| “Which acquisition channels bring the highest-converting users?” | acquisition_channel | users + orders |
| “Where in the funnel are users dropping off?” | event_name · user_id | events |
| “Did the experiment’s treatment group convert better?” | variant · user_id | experiments + orders |
| “Which signup cohorts have the best 30-day retention?” | signup_date · event_timestamp | users + events |
| “What is average revenue per paying user?” | total_amount · user_id | orders |
How users, events, sessions, orders, and experiments are structured and how they reflect real product behavior in apps, platforms, and e-commerce systems.
Querying the events table to understand what actions users are taking — filtering by event_name, counting interactions, and exploring behavioral patterns.
Analyzing session data — duration, traffic source, and session frequency — to understand how users engage with the product across visits.
Using COUNT, SUM, and AVG to calculate daily active users, session totals, average duration, and other foundational product KPIs.
Using signup_date and event timestamps to distinguish first-time users from returning ones — a fundamental segmentation in all product analytics.
Handling NULL values, filtering invalid records, and preparing clean datasets before analysis — the essential first step of any production-quality query.
SELECT, FROM, WHERE, ORDER BY, LIMIT, COUNT(), SUM(), AVG(), IS NULL, basic date filtering, column aliases, DISTINCT.
Measuring how many users return after their first visit or purchase — day-1, day-7, and day-30 retention rates that reveal product stickiness.
Building step-by-step conversion funnels using event sequences — identifying where users drop off between signup, engagement, and purchase.
Grouping users by signup month and tracking their behavior over subsequent periods — the gold standard for measuring product improvement over time.
Measuring how frequently users engage with specific features, identifying power users, and tracking which product areas drive the most activity.
Sequencing user events to understand the most common paths taken before conversion — and where journeys stall or diverge.
Joining experiment assignments to behavioral and revenue outcomes to determine whether a product change improved the metrics it was designed to move.
INNER JOIN, LEFT JOIN, GROUP BY, HAVING, DATE_FORMAT(), date arithmetic, COUNT(DISTINCT), multi-table joins, calculated conversion rates.
Classifying users into lifecycle stages — new, active, at-risk, churned — and analyzing behavior and revenue by segment to prioritize growth actions.
Tracking week-over-week and month-over-month growth in users, sessions, and revenue — identifying acceleration, plateaus, and inflection points.
Deep-diving into funnel drop-off — comparing conversion across device types, traffic sources, user segments, and time periods to find optimization opportunities.
Average revenue per user, lifetime value by cohort, product revenue breakdown, and identifying the user segments that generate the most monetization value.
Comparing control vs. treatment group outcomes across engagement, conversion, and revenue metrics — producing the statistical summary that determines experiment winners.
Building complete product analytics reports — structured for weekly reviews, growth meetings, and executive presentations — that translate SQL outputs into product decisions.
CASE WHEN for segmentation, subqueries, window functions, LAG() for trend analysis, revenue calculations, advanced GROUP BY, EXTRACT() for date parts, experiment comparison logic.
Overview — Key Takeaways
Five foundational principles from this introductory section.
Users, events, sessions, orders, payments, order_items, products, and experiments — covering the full user lifecycle from first visit to revenue to A/B test evaluation.
User, session, event, and transaction-level data. Knowing which level a table belongs to — and joining correctly across those levels — prevents inflated counts and misleading product metrics.
user_id, event_name, event_timestamp, signup_date, acquisition_channel, variant, and total_amount are the backbone of product and growth SQL — the fields that answer every question from DAU to LTV.
Foundations, behavioral analysis, and growth decision-making — a natural progression that builds from basic event queries to cohort analysis, funnel diagnostics, and experiment evaluation.
Conceptual clarity prevents analytical errors. Understanding how product data flows — from user signup through behavioral events to transactions — produces accurate, trustworthy insights from the very first query.
