funnel_report.sql en SQL

Contar cuántas sesiones llegaron a cada paso, y luego la caída entre ellos.

-- Embudo de sesiones: visita, registro, primer pedido, pedido repetido.

WITH stage_events AS (
    SELECT
        session_id,
        kind,
        occurred_at
    FROM events
    WHERE occurred_at >= DATE('now', '-30 days')
),
stages AS (
    SELECT
        1 AS step,
        'visited' AS stage,
        COUNT(DISTINCT session_id) AS sessions
    FROM stage_events
    UNION ALL
    SELECT
        2,
        'signed up',
        COUNT(DISTINCT session_id)
    FROM stage_events
    WHERE kind = 'signup'
    UNION ALL
    SELECT
        3,
        'ordered',
        COUNT(DISTINCT session_id)
    FROM stage_events
    WHERE kind = 'order'
    UNION ALL
    SELECT
        4,
        'ordered again',
        COUNT(DISTINCT session_id)
    FROM stage_events
    WHERE kind = 'reorder'
)
SELECT
    step,
    stage,
    sessions,
    LAG(sessions) OVER (
        ORDER BY step
    ) AS previous_stage,
    ROUND(
        sessions * 100.0 / NULLIF(
            LAG(sessions) OVER (
                ORDER BY step
            ),
            0
        ),
        1
    ) AS conversion_pct,
    ROUND(
        sessions * 100.0 / NULLIF(
            FIRST_VALUE(sessions) OVER (
                ORDER BY step
            ),
            0
        ),
        1
    ) AS of_all_sessions
FROM stages
ORDER BY step;

Cómo funciona

Una CTE por etapa deja cada COUNT(DISTINCT ...) fácil de revisar.
Las etapas se apilan con UNION ALL para compartir un solo orden.
LAG sobre las filas apiladas convierte conteos en tasa de conversión.

Palabras clave y builtins usados aquí

ALL
AS
BY
COUNT
DATE
DISTINCT
FROM
NULLIF
ORDER
SELECT
UNION
WHERE
WITH

El intento, en números

Líneas: 65
Caracteres a escribir: 925
Tokens: 184
Ritmo de tres estrellas: 95 tpm

Al ritmo de tres estrellas de 95 tokens por minuto, este intento toma unos 116 segundos.

Escribe este fragmento

Paso 2 de 2 en Bis; paso 22 de 22 en Joins y agregación.

← Anterior