SQL for Data Analytics: KPI, Retention, Cohort and Funnel Queries

Product and data teams live on a handful of query shapes: KPIs over time, retention (do users come back?), cohorts (how does each sign-up month behave?) and funnels (where do users drop off?). These are also the most common FAANG-style SQL questions. Each is shown as a complete query you can adapt.

KPI Queries

-- Monthly Revenue KPI
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS revenue,
    ROUND(SUM(amount)/COUNT(DISTINCT order_id), 2) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

-- Month-over-Month Growth
WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
           SUM(amount) AS revenue
    FROM orders GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
            / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS mom_growth_pct
FROM monthly;

User Retention Analysis

-- Day-1 and Day-7 Retention
WITH first_activity AS (
    SELECT user_id, MIN(event_date) AS first_date
    FROM user_events
    GROUP BY user_id
)
SELECT
    f.first_date AS cohort_date,
    COUNT(DISTINCT f.user_id) AS new_users,
    COUNT(DISTINCT CASE WHEN e.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
                         THEN e.user_id END) AS day1_retained,
    COUNT(DISTINCT CASE WHEN e.event_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
                         THEN e.user_id END) AS day7_retained
FROM first_activity f
LEFT JOIN user_events e ON f.user_id = e.user_id
GROUP BY f.first_date
ORDER BY f.first_date;

Cohort Analysis

-- Weekly cohort retention
WITH cohorts AS (
    SELECT user_id,
           DATE_TRUNC('week', MIN(order_date)) AS cohort_week
    FROM orders
    GROUP BY user_id
),
user_activity AS (
    SELECT o.user_id, c.cohort_week,
           FLOOR(DATEDIFF(o.order_date, c.cohort_week) / 7) AS week_number
    FROM orders o
    JOIN cohorts c ON o.user_id = c.user_id
    GROUP BY o.user_id, c.cohort_week, week_number
)
SELECT cohort_week, week_number,
       COUNT(DISTINCT user_id) AS active_users
FROM user_activity
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;

Funnel Analysis

-- E-commerce conversion funnel
SELECT
    COUNT(DISTINCT CASE WHEN event_type = 'page_view'    THEN user_id END) AS page_views,
    COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart'  THEN user_id END) AS add_to_cart,
    COUNT(DISTINCT CASE WHEN event_type = 'checkout'     THEN user_id END) AS checkout,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase'     THEN user_id END) AS purchases,
    -- Conversion rates
    ROUND(COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) * 100.0
        / NULLIF(COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN user_id END),0), 2) AS pv_to_cart_pct,
    ROUND(COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) * 100.0
        / NULLIF(COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN user_id END),0), 2) AS overall_conversion_pct
FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';

Monthly Active Users and Other FAANG Patterns

FAANG Pattern: Monthly Active Users (MAU)

SELECT DATE_FORMAT(event_date, '%Y-%m') AS month,
       COUNT(DISTINCT user_id) AS MAU
FROM user_events
GROUP BY month
ORDER BY month;

-- DAU/MAU Ratio (engagement metric)
WITH dau AS (
    SELECT event_date, COUNT(DISTINCT user_id) AS daily_active
    FROM user_events GROUP BY event_date
),
mau AS (
    SELECT DATE_FORMAT(event_date, '%Y-%m') AS month,
           COUNT(DISTINCT user_id) AS monthly_active
    FROM user_events GROUP BY month
)
SELECT d.event_date,
       d.daily_active AS DAU,
       m.monthly_active AS MAU,
       ROUND(d.daily_active * 100.0 / m.monthly_active, 2) AS dau_mau_ratio
FROM dau d
JOIN mau m ON DATE_FORMAT(d.event_date, '%Y-%m') = m.month
ORDER BY d.event_date;

FAQs

How do you calculate retention in SQL?

Find each user's first activity date, join back to their later activity, and count users active exactly N days later (or within a window), divided by the users in that cohort.

What is cohort analysis?

Grouping users by when they started (for example sign-up month) and tracking a metric for each group over the following periods, which shows whether newer users behave better or worse.

How do you build a funnel query?

Count distinct users who reached each step (often with conditional aggregation), then divide each step by the previous one to get the conversion rate.