Rapport SAE-Web – Mission 3

Mission 3 : Analyse de la base KDou (nouvelle version)

I) Contexte et objectifs de la mission 3

Cette mission consiste à exploiter la nouvelle version de la base de données KDou, dans laquelle ont été ajoutés de nouveaux champs stratégiques :

Informations sur les pays (UE / Hors UE, devise…)
Coût d’achat des produits et coefficient de marge par catégorie
Relations spéciales entre clients (clients pilotes et clients partenaires)

L’objectif est de construire des indicateurs de pilotage utiles pour KDou afin de mieux analyser son chiffre d’affaires, optimiser le catalogue et vérifier la cohérence de ses relations commerciales.

II) Requêtes imposées

1) Chiffre d’affaires des desserts et boissons par année

Énoncé : Calculer, pour chaque année, le chiffre d’affaires généré par les catégories Boissons et Desserts.

Construction : On utilise le coût d’achat du produit, le coefficient de marge de la catégorie, la quantité commandée et la remise pour obtenir le chiffre d’affaires. On ne garde que les lignes dont la catégorie contient “dessert” ou “boissons” puis on groupe par année de commande.

Requête SQL :


                SELECT
                SUM(
                P.CoutAchat * CA.CoefMarge * DE.Quantité * (1 - DE.[Remise (%)])
                ) AS ChiffreAffaire,
                Year(C.DateCde) AS Annee
                FROM
                (
                (Categorie AS CA
                INNER JOIN Produit AS P ON CA.CodeCateg = P.CodeCateg)
                INNER JOIN DetailCommande AS DE ON P.RefProd = DE.[Réf produit]
                )
                INNER JOIN Commande AS C ON C.[N° commande] = DE.[N° commande]
                WHERE
                CA.NomCateg LIKE "*boissons*"
                OR CA.NomCateg LIKE "*dessert*"
                GROUP BY
                Year(C.DateCde);

Résultat : On obtient, pour chaque année, le chiffre d’affaires réalisé avec les desserts et boissons. KDou peut ainsi suivre l’évolution de ces ventes et repérer les années les plus rentables.

2) Nombre de produits carnés non commandés

Énoncé : Déterminer quels produits carnés (viande / poisson) n’ont jamais été commandés.

Construction : On considère comme “carnés” les produits dont la catégorie contient Viande ou Poisson. Un produit est “non commandé” s’il n’apparaît dans aucune ligne de DetailCommande. On utilise donc un LEFT JOIN de Produit vers DetailCommande et on cherche les lignes où le numéro de commande est NULL.

Requête SQL :


                SELECT
                P.NomProd AS Nom
                FROM
                (
                (Produit AS P
                INNER JOIN Categorie AS C ON P.CodeCateg = C.CodeCateg)
                LEFT JOIN DetailCommande AS D ON P.RefProd = D.[Réf produit]
                )
                WHERE
                (
                C.NomCateg LIKE "*Viande*"
                OR C.NomCateg LIKE "*Poisson*"
                )
                AND D.[N° commande] IS NULL
                ORDER BY
                1;

Résultat : La requête liste les produits carnés jamais commandés. Cela permet à KDou d’identifier les références à faible intérêt commercial et de réfléchir à une suppression ou substitution.

3) Chiffre d’affaires avec les fournisseurs hors UE

Énoncé : « Quel CA total a été généré à ce jour avec les fournisseurs hors UE ? »

Construction : On ne garde que les fournisseurs dont le pays est marqué "Hors" dans le champ UE. On recalcule le chiffre d’affaires comme précédemment et on groupe par pays et devise pour voir la contribution de chaque zone.

Requête SQL :


                SELECT
                Pays.PaysFR,
                SUM(
                P.CoutAchat * Categ.CoefMarge * DE.Quantité * (1 - DE.[Remise (%)])
                ) AS ChiffreAffaire,
                Pays.Devise,
                COUNT(DE.[N° commande]) AS NbCommandes
                FROM
                (
                (
                (
                Pays
                INNER JOIN Fournisseur ON Pays.NoPays = Fournisseur.NoPays
                )
                INNER JOIN Produit AS P ON Fournisseur.NoFour = P.NoFour
                )
                INNER JOIN Categorie AS Categ ON P.CodeCateg = Categ.CodeCateg
                )
                INNER JOIN DetailCommande AS DE ON P.RefProd = DE.[Réf produit]
                WHERE
                Pays.UE = "Hors"
                GROUP BY
                Pays.PaysFR,
                Pays.Devise
                ORDER BY
                1 DESC;

Résultat : La requête donne, par pays hors UE, le chiffre d’affaires, la devise utilisée et le nombre de commandes. KDou peut ainsi évaluer la rentabilité de ses importations extra-européennes.

4) Comparaison clients pilotes / clients partenaires

Énoncé : « Y a-t-il des clients pilotes ayant autant de commandes que leurs clients partenaires ? »

Construction : Un client pilote est identifié par son code, repris dans le champ CodeCliPart des clients partenaires. On calcule d’abord le nombre de commandes par client, puis on compare pour chaque couple (pilote, partenaire) le volume de commandes, en ne gardant que les cas où les deux ont le même nombre.

Requête SQL :


                SELECT
                Pilote.CodeCli AS CodePilote,
                Pilote.Societe AS SocietePilote,
                CmdPilote.NbCmd AS CommandesPilote,
                Partenaire.CodeCli AS CodePartenaire,
                Partenaire.Societe AS SocietePartenaire,
                CmdPart.NbCmd AS CommandesPartenaire
                FROM
                (
                (
                Clientele AS Partenaire
                INNER JOIN Clientele AS Pilote
                ON Partenaire.CodeCliPart = Pilote.CodeCli
                )
                LEFT JOIN (
                SELECT
                CodeCli,
                COUNT(*) AS NbCmd
                FROM
                Commande
                GROUP BY
                CodeCli
                ) AS CmdPilote ON Pilote.CodeCli = CmdPilote.CodeCli
                )
                LEFT JOIN (
                SELECT
                CodeCli,
                COUNT(*) AS NbCmd
                FROM
                Commande
                GROUP BY
                CodeCli
                ) AS CmdPart ON Partenaire.CodeCli = CmdPart.CodeCli
                WHERE
                CmdPilote.NbCmd = CmdPart.NbCmd;

Résultat : On obtient la liste des couples pilote/partenaire avec le même nombre de commandes. KDou peut vérifier si le réseau de pilotes joue bien son rôle ou si certains partenariats doivent être ajustés.

III) Indicateurs proposés par l’équipe

Chaque membre du groupe a proposé un indicateur supplémentaire à partir de la base enrichie, afin d’aider la société KDou à mieux piloter son activité.

A) Neche Younes – Clients qui commandent le plus

Énoncé : Identifier les clients qui passent le plus grand nombre de commandes.

Intérêt pour KDou : repérer les clients à forte valeur, optimiser les remises de fidélité, les offres promotionnelles et mieux prévoir la demande en analysant les habitudes de commande.

Requête SQL :


                SELECT
                Clientele.Societe,
                COUNT(Commande.[N° commande]) AS NbCommandes
                FROM
                Clientele
                INNER JOIN Commande ON Clientele.CodeCli = Commande.CodeCli
                GROUP BY
                Clientele.Societe
                ORDER BY
                2 DESC;

Résultat : La requête met en avant les clients les plus actifs. KDou peut concentrer ses efforts commerciaux sur ces clients stratégiques.

B) Chabbi Brahim – Nombre de produits par fournisseur

Énoncé : Compter, pour chaque fournisseur, le nombre de références produits au catalogue.

Intérêt pour KDou : identifier les fournisseurs clés (offre large) et ceux qui sont peu représentés, pour décider d’éventuels partenariats, renégociations ou diversifications.

Requête SQL :


                SELECT
                Fournisseur.Societe,
                Count(Produit.RefProd) AS NombreProduits
                FROM
                Fournisseur
                LEFT JOIN Produit ON Fournisseur.NoFour = Produit.NoFour
                GROUP BY
                Fournisseur.Societe
                ORDER BY
                Count(Produit.RefProd) DESC;

Résultat : Tous les fournisseurs apparaissent, même sans produit (grâce au LEFT JOIN). On visualise rapidement les acteurs majeurs du catalogue de KDou.

C) Kapusta Alexandre – Nombre de produits par pays d’origine

Énoncé : Compter le nombre de références produits associées à chaque pays fournisseur.

Intérêt pour KDou : suivre la répartition géographique de l’offre, mesurer la diversification des approvisionnements et réfléchir à l’impact environnemental des transports.

Requête SQL :


                SELECT
                Pays.PaysFR,
                Count(Produit.RefProd) AS NombreProduits
                FROM
                (
                Produit
                INNER JOIN Fournisseur ON Produit.NoFour = Fournisseur.NoFour
                )
                INNER JOIN Pays ON Fournisseur.NoPays = Pays.NoPays
                GROUP BY
                Pays.PaysFR
                ORDER BY
                Count(Produit.RefProd) DESC;

Résultat : Les pays les plus représentés dans le catalogue sont mis en évidence. KDou peut négocier différemment selon les zones ou renforcer la diversité de certains pays sous-représentés.

D) Toumi Mohamed – Total des ventes par produit (Desserts et Boissons)

Énoncé : Mesurer, pour chaque produit des catégories Desserts et Boissons, la quantité totale vendue.

Intérêt pour KDou : mettre en évidence les meilleures ventes et les produits en difficulté dans ces catégories, afin d’ajuster l’offre, cibler les promotions et envisager le remplacement des produits les moins vendus.

Requête SQL :


                SELECT
                Categorie.NomCateg,
                Produit.NomProd,
                Sum(DetailCommande.[Quantité]) AS TotalVentes
                FROM
                (
                Categorie
                INNER JOIN Produit ON Categorie.CodeCateg = Produit.CodeCateg
                )
                INNER JOIN DetailCommande ON Produit.RefProd = DetailCommande.[Réf produit]
                WHERE
                Categorie.NomCateg IN ('Desserts', 'Boissons')
                GROUP BY
                Categorie.NomCateg,
                Produit.NomProd
                ORDER BY
                Sum(DetailCommande.[Quantité]) DESC;

Résultat : Le tri décroissant fait apparaître immédiatement les produits les plus demandés. KDou peut y associer des remises, et réfléchir au remplacement des produits très peu vendus.