Ventes — Retail & e-commerce

Question métier : quels points de vente sont en croissance ou en décroissance sur la période, pour prioriser les actions commerciales ?

WITH ventes_mensuelles AS (
    SELECT point_vente_id, DATE_TRUNC('month', mois) AS mois,
           SUM(commandes) AS commandes
    FROM fact_ventes
    GROUP BY 1, 2
),
bornes AS (
    SELECT
        point_vente_id,
        FIRST_VALUE(commandes) OVER (
            PARTITION BY point_vente_id ORDER BY mois
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS debut,
        LAST_VALUE(commandes) OVER (
            PARTITION BY point_vente_id ORDER BY mois
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fin
    FROM ventes_mensuelles
)
SELECT DISTINCT
    p.nom, debut, fin,
    ROUND(100.0 * (fin - debut) / NULLIF(debut, 0), 1) AS croissance_pct
FROM bornes b
JOIN dim_point_vente p ON p.id_point_vente = b.point_vente_id
ORDER BY croissance_pct DESC;