Rapports courants sur les dates (MTD, WoW, YoY)

Trois rapports classiques par période

Depuis le début du mois

SELECT SUM(total) AS mtd_revenue
FROM orders
WHERE created_at >= date_trunc('month', NOW())
  AND created_at <  date_trunc('month', NOW()) + INTERVAL '1 month';

Depuis le début de l'année

SELECT SUM(total) AS ytd_revenue
FROM orders
WHERE created_at >= date_trunc('year', NOW());

Semaine sur semaine

SELECT
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW())
  ) AS this_week,
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week'
      AND created_at <  date_trunc('week', NOW())
  ) AS last_week
FROM orders
WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week';

Année sur année

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
WHERE created_at >= NOW() - INTERVAL '2 years'
GROUP BY 1
ORDER BY 1;

-- Then plot with two lines: current year, previous year.

Série quotidienne sans trous

SELECT day, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(
  CURRENT_DATE - INTERVAL '30 days',
  CURRENT_DATE,
  INTERVAL '1 day'
) AS day
LEFT JOIN orders o ON date_trunc('day', o.created_at) = day
GROUP BY day
ORDER BY day;

Totaux cumulés

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

Écart d'une période à l'autre

SELECT month,
       revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year,
       (revenue - LAG(revenue, 12) OVER (ORDER BY month))
         / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) AS yoy_growth
FROM monthly_revenue;

7 / 30 derniers jours (fenêtre glissante)

-- Trailing 7 days, ending now:
WHERE created_at >= NOW() - INTERVAL '7 days';

-- This calendar week, from Monday:
WHERE created_at >= date_trunc('week', NOW());

Considérations relatives aux fuseaux horaires

SELECT date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS la_day,
       COUNT(*)
FROM events
GROUP BY 1;

Pré-agréger avec des vues matérialisées

Récapitulatif

Vérification rapide