Modèles de rapports du monde réel

Modèle : top N par groupe

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

Modèle : totaux cumulés

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

Modèle : première occurrence

SELECT user_id, action, MIN(ts) AS first_at
FROM events
GROUP BY user_id, action;

-- Or with window functions for full row:
WITH firsts AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, action ORDER BY ts) AS rn
  FROM events
)
SELECT * FROM firsts WHERE rn = 1;

Modèle : fidélisation par cohorte

WITH cohorts AS (
  SELECT id AS user_id, date_trunc('week', created_at) AS cohort_week
  FROM users
),
activities AS (
  SELECT user_id, date_trunc('week', ts) AS active_week FROM events
)
SELECT c.cohort_week,
       (a.active_week - c.cohort_week) / 7 AS week_offset,
       COUNT(DISTINCT a.user_id) AS active
FROM cohorts c
JOIN activities a USING (user_id)
WHERE a.active_week >= c.cohort_week
GROUP BY c.cohort_week, week_offset
ORDER BY c.cohort_week, week_offset;

Modèle : analyse de l'entonnoir

SELECT
  COUNT(*)                                  AS signed_up,
  COUNT(*) FILTER (WHERE first_login_at IS NOT NULL) AS logged_in,
  COUNT(*) FILTER (WHERE first_purchase_at IS NOT NULL) AS purchased
FROM users;

Modèle : création de sessions

WITH gaps AS (
  SELECT user_id, ts,
    CASE
      WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
           > INTERVAL '30 min'
      THEN 1 ELSE 0
    END AS new_session
  FROM events
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;

Modèle : comparaison entre périodes

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
       (revenue::FLOAT / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 1) * 100 AS pct_change
FROM monthly_revenue
ORDER BY month;

Modèle : résultat sous forme de tableau croisé

SELECT user_id,
       SUM(amount) FILTER (WHERE month = '2024-01') AS jan,
       SUM(amount) FILTER (WHERE month = '2024-02') AS feb,
       SUM(amount) FILTER (WHERE month = '2024-03') AS mar
FROM monthly_spend
GROUP BY user_id;

Modèle : utilisateurs actifs aujourd'hui

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '1 day')  AS dau,
  COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '7 days') AS wau,
  COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '30 days') AS mau
FROM events;

Modèle : combler les lacunes

SELECT day, COALESCE(COUNT(e.id), 0) AS events
FROM generate_series(CURRENT_DATE - 30, CURRENT_DATE, INTERVAL '1 day') AS day
LEFT JOIN events e ON date_trunc('day', e.ts) = day
GROUP BY day
ORDER BY day;

Combiner les fonctions de fenêtre pour obtenir des informations

SELECT day, revenue,
       LAG(revenue) OVER w               AS prev,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
       SUM(revenue) OVER (ORDER BY day)  AS running_total
FROM daily_revenue
WINDOW w AS (ORDER BY day)
ORDER BY day;

Récapitulatif

Vérification rapide