Cas pratique - Analyse des ventes avec Window Functions SQL

ALTER TABLE clean_sales ADD COLUMN Sale_Date_ISO TEXT;

UPDATE clean_sales 
SET Sale_Date_ISO = SUBSTR(Sale_Date_Clean, 7, 4) || '-' || 
                   SUBSTR(Sale_Date_Clean, 1, 2) || '-' || 
                   SUBSTR(Sale_Date_Clean, 4, 2)
WHERE Sale_Date_Clean LIKE '__-__-____';

-- 1. Table de correspondance (produit → catégorie la plus fréquente)
CREATE TABLE product_category_map AS
SELECT 
    Product_Name,
    Product_Category,
    COUNT(*) AS freq,
    ROW_NUMBER() OVER (PARTITION BY Product_Name ORDER BY COUNT(*) DESC) AS rn
FROM clean_sales
WHERE Product_Category IS NOT NULL
GROUP BY Product_Name, Product_Category;

-- 2. Garder seulement la catégorie la plus fréquente par produit
CREATE TABLE product_category_final AS
SELECT Product_Name, Product_Category
FROM product_category_map
WHERE rn = 1;

-- 3. Mise à jour des NULL
UPDATE clean_sales
SET Product_Category = (
    SELECT Product_Category 
    FROM product_category_final 
    WHERE product_category_final.Product_Name = clean_sales.Product_Name
)
WHERE Product_Category IS NULL;

SELECT 
    COALESCE(Store_Location, 'Inconnu') AS Store_Location,
    Sale_Date_Clean,
    Total_Amount,
    SUM(Total_Amount) OVER (
        PARTITION BY COALESCE(Store_Location, 'Inconnu')
        ORDER BY Sale_Date_Clean
    ) AS cumul_ventes
FROM clean_sales
ORDER BY COALESCE(Store_Location, 'Inconnu'), Sale_Date_Clean
LIMIT 30;

SELECT 
    Sale_Date_ISO,
    ROUND(SUM(Total_Amount), 2) AS ventes_journalieres,
    ROUND(LAG(SUM(Total_Amount), 1) OVER (ORDER BY Sale_Date_ISO), 2) AS ventes_veille,
    ROUND(SUM(Total_Amount) - LAG(SUM(Total_Amount), 1) OVER (ORDER BY Sale_Date_ISO), 2) AS variation
FROM clean_sales
WHERE Sale_Date_ISO IS NOT NULL
GROUP BY Sale_Date_ISO
ORDER BY Sale_Date_ISO
LIMIT 30;

SELECT 
    Product_Category,
    Product_Name,
    ROUND(SUM(Total_Amount), 2) AS total_ventes,
    RANK() OVER (PARTITION BY Product_Category ORDER BY SUM(Total_Amount) DESC) AS rang
FROM clean_sales
GROUP BY Product_Category, Product_Name
ORDER BY Product_Category, rang
LIMIT 30;

SELECT 
    Sale_Date_ISO,
    ROUND(Total_Amount, 2) AS Total_Amount,
    ROUND(AVG(Total_Amount) OVER (
        ORDER BY Sale_Date_ISO
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS moyenne_mobile_7j
FROM clean_sales
WHERE Sale_Date_ISO IS NOT NULL
ORDER BY Sale_Date_ISO
LIMIT 30;

SELECT 
    COALESCE(Store_Location, 'Inconnu') AS Store_Location,
    ROUND(SUM(Total_Amount), 2) AS ventes_region,
    ROUND(100.0 * SUM(Total_Amount) / SUM(SUM(Total_Amount)) OVER (), 2) AS part_pourcentage
FROM clean_sales
GROUP BY COALESCE(Store_Location, 'Inconnu')
ORDER BY ventes_region DESC;

SELECT * FROM (
    SELECT 
        Product_Category,
        Product_Name,
        ROUND(SUM(Total_Amount), 2) AS total_ventes,
        RANK() OVER (PARTITION BY Product_Category ORDER BY SUM(Total_Amount) DESC) AS rang
    FROM clean_sales
    GROUP BY Product_Category, Product_Name
) AS ranked
WHERE rang <= 3
ORDER BY Product_Category, rang;

WITH ventes_par_mois AS (
    SELECT 
        strftime('%Y-%m', Sale_Date_ISO) AS mois,
        ROUND(SUM(Total_Amount), 2) AS ventes_mois
    FROM clean_sales
    WHERE Sale_Date_ISO IS NOT NULL
    GROUP BY strftime('%Y-%m', Sale_Date_ISO)
)
SELECT 
    mois,
    ventes_mois,
    LAG(ventes_mois, 12) OVER (ORDER BY mois) AS ventes_annee_precedente,
    ROUND(100.0 * (ventes_mois - LAG(ventes_mois, 12) OVER (ORDER BY mois)) 
          / NULLIF(LAG(ventes_mois, 12) OVER (ORDER BY mois), 0), 2) AS evolution_pct
FROM ventes_par_mois
ORDER BY mois;