Every growth question, from "where do users drop off?" and "did the campaign work?" to "which channel brings customers who stay?", is answered from event data with SQL. Analysts who can write those queries themselves move at the speed of the marketing team.
This path is built around the analyses growth teams run every week: funnels, retention cohorts, sessions, experiments, segmentation and attribution. You'll also learn the less glamorous part that decides whether any of it is right: cleaning and de-duplicating event data before you count it.
Tracking data is messy: duplicate events from retries, test users, bots and inconsistent event names. Before any funnel or cohort, build a clean events view that removes duplicates and internal traffic. Every metric downstream inherits its accuracy from this step.
CREATE VIEW clean_events AS
SELECT user_id, event_name, event_time, channel
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) AS rn
FROM raw_events
WHERE user_id NOT IN (SELECT user_id FROM internal_users)
) e
WHERE rn = 1;A funnel counts how many users reach each step in order: visit, sign-up, first purchase. Conditional aggregation per user gives each user's furthest step; summing those flags gives the funnel, and dividing neighbouring steps gives conversion rates.
WITH steps AS (
SELECT user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS visited,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS signed_up,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
FROM clean_events
GROUP BY user_id
)
SELECT SUM(visited) AS visitors, SUM(signed_up) AS signups, SUM(purchased) AS buyers,
ROUND(100.0 * SUM(purchased) / NULLIF(SUM(visited), 0), 1) AS visit_to_buy_pct
FROM steps;Cohort analysis groups users by when they started (their sign-up month) and tracks what share are still active in each later month. It separates "are we acquiring more users?" from "are users sticking around?", which totals blur together.
WITH first_month AS (
SELECT user_id, MIN(DATE_TRUNC('month', event_time)) AS cohort_month
FROM clean_events GROUP BY user_id
),
activity AS (
SELECT DISTINCT user_id, DATE_TRUNC('month', event_time) AS active_month
FROM clean_events
)
SELECT f.cohort_month,
DATEDIFF('month', f.cohort_month, a.active_month) AS months_since_start,
COUNT(DISTINCT a.user_id) AS active_users
FROM first_month f JOIN activity a ON a.user_id = f.user_id
GROUP BY 1, 2
ORDER BY 1, 2;Most event data has no session ID, so you build sessions yourself: a new session starts when the gap since a user's previous event exceeds a threshold, commonly 30 minutes. LAG() finds the gap, and a running SUM() of "new session" flags numbers the sessions.
WITH gaps AS (
SELECT user_id, event_time,
CASE WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
> INTERVAL '30 minutes'
OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
THEN 1 ELSE 0 END AS new_session
FROM clean_events
)
SELECT user_id, event_time,
SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_number
FROM gaps;An experiment compares a metric between randomly assigned groups. SQL produces the per-variant numbers (users, conversions, conversion rate) and checks the basics: groups are roughly the expected size and each user saw only one variant. The statistical significance test usually happens in a notebook or calculator on top.
SELECT a.variant,
COUNT(DISTINCT a.user_id) AS users,
COUNT(DISTINCT o.user_id) AS converters,
ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT a.user_id), 2) AS conversion_pct
FROM experiment_assignments a
LEFT JOIN orders o
ON o.user_id = a.user_id AND o.order_time >= a.assigned_at
WHERE a.experiment = 'checkout_v2'
GROUP BY a.variant;Not all customers are equal. RFM scores customers on Recency, Frequency and Monetary value; NTILE() splits each measure into quintiles so you can target "champions" differently from "at risk" customers. Lifetime value sums what a customer has spent so far, or per cohort over time.
WITH rfm AS (
SELECT customer_id,
MAX(order_date) AS last_order, COUNT(*) AS orders, SUM(amount) AS spend
FROM orders GROUP BY customer_id
)
SELECT customer_id,
NTILE(5) OVER (ORDER BY last_order) AS r_score,
NTILE(5) OVER (ORDER BY orders) AS f_score,
NTILE(5) OVER (ORDER BY spend) AS m_score
FROM rfm;Attribution decides which marketing touchpoint gets credit for a conversion. First-touch credits the channel that brought the user in; last-touch credits the final one before purchase. ROW_NUMBER() over each user's touchpoints picks either in one query.
WITH ranked AS (
SELECT user_id, channel,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time ASC) AS first_rank,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time DESC) AS last_rank
FROM touchpoints
)
SELECT channel,
SUM(CASE WHEN first_rank = 1 THEN 1 ELSE 0 END) AS first_touch_conversions,
SUM(CASE WHEN last_rank = 1 THEN 1 ELSE 0 END) AS last_touch_conversions
FROM ranked
WHERE user_id IN (SELECT user_id FROM orders)
GROUP BY channel;"Top 3 campaigns per channel by revenue" is a ranking within groups. Rank inside each channel with a window function, then filter on the rank. It's one of the most frequently asked questions in marketing analytics and in interviews.
SELECT channel, campaign, revenue
FROM (
SELECT channel, campaign, SUM(revenue) AS revenue,
DENSE_RANK() OVER (PARTITION BY channel ORDER BY SUM(revenue) DESC) AS rnk
FROM campaign_results
GROUP BY channel, campaign
) t
WHERE rnk <= 3;ROW_NUMBER, RANK or DENSE_RANK deliberately. With ties, they return different numbers of rows for "top 3".Practice growth analyses on realistic product and order data.
Growth and product analyst interviews love funnels, retention and "top N per group" questions, usually framed as a business scenario.
25 questions covering everything above. Score 80% or higher to earn your Marketing / Growth Analyst badge on your dashboard.
25 questions · pass at 80%+ to earn your Marketing / Growth Analyst badge on your dashboard. One-time payment, lifetime access.