La question
Une compagnie d’assurance habitation conservait ses contrats dans deux fichiers plats : une ligne par contrat, et une liste de référence des communes françaises. Elle voulait les réunir dans une base de données relationnelle que ses équipes pourraient interroger, et obtenir des réponses à douze questions métier : où vivent ses clients, ce qu’ils assurent, et combien ils paient.
C’était mon premier projet SQL : concevoir la base, y charger les deux fichiers, puis répondre aux douze questions.
- 30 335contrats d’assurance habitation
- 12questions métier traitées en SQL
- 47 %des contrats en Île-de-France
- 19,33 €de cotisation mensuelle moyenne
Construire la base de données
J’ai commencé par un dictionnaire de données recensant chaque champ avec son type, sa taille et son rôle, puis j’ai conçu deux tables reliées par une clé étrangère : chaque contrat pointe vers la commune où se trouve le logement assuré, et la table des communes contient son département et sa région. On évite ainsi de répéter les mêmes informations géographiques sur 30 335 lignes.
Les noms de colonnes sont repris tels quels des fichiers sources.
Côté RGPD, la table des contrats contenait l’adresse postale de chaque logement assuré. Croisée avec une commune, une surface et une cotisation, une adresse peut permettre d’identifier une personne : la version publique de la base laisse donc de côté les quatre colonnes d’adresse.
Qui sont les clients
Le portefeuille se compose surtout d’appartements (92 %) et de résidences principales (84 %), et 70 % des assurés sont propriétaires de leur logement. À Paris, la surface moyenne des logements assurés est de 51,8 m², et les cinq plus grandes surfaces du portefeuille vont de 559 à 815 m². Géographiquement, le portefeuille est fortement concentré en région parisienne.
- Île-de-France14 177
- Provence-Alpes-Côte d’Azur3 279
- Auvergne-Rhône-Alpes3 042
- Nouvelle-Aquitaine2 038
- Occitanie1 609
- Pays de la Loire1 196
L’Île-de-France concentre à elle seule 47 % des contrats. Les arrondissements de Paris représentent l’essentiel des communes comptant au moins 150 contrats.
Les cotisations
La cotisation mensuelle moyenne est de 19,33 €. Par département, Paris arrive nettement en tête, à 36,40 € par mois, presque deux fois la moyenne, devant les Hauts-de-Seine (26,27 €) et le Val-de-Marne (19,82 €). Pour ce classement, une jointure relie chaque contrat à sa commune, et donc à son département :
-- Top 10 des départements par cotisation mensuelle moyenne
SELECT region.dep_nom,
ROUND(AVG(contrat.Prix_cotisation_mensuel), 2) AS average_premium,
COUNT(*) AS contracts
FROM contrat
JOIN region
ON contrat.Code_dep_code_commune = region.Code_dep_code_commune
GROUP BY region.dep_nom
ORDER BY average_premium DESC
LIMIT 10;La valeur déclarée des biens dessine un portefeuille surtout modeste : les trois quarts des contrats déclarent moins de 25 000 € de biens.
- 0–25 000 €22 720
- 25–50 000 €6 815
- 50–100 000 €696
- 100 000 € +104
Limites
- Une photographie unique : les données ne sont pas datées, elles ne disent donc rien de l’évolution du portefeuille ni des prix.
- Les valeurs déclarées sont exprimées en tranches, pas en montants, ce qui limite la finesse de l’analyse.
Ce que j’en retiens
C’était mon premier projet SQL. J’y ai appris à traduire une question métier en requête : chaque question demandait de choisir la bonne table, le bon filtre et le bon niveau de regroupement, du simple comptage des résidences principales jusqu’à la jointure entre contrats et communes pour classer les départements. J’ai aussi compris l’intérêt d’un schéma normalisé : les informations géographiques ne sont stockées qu’une fois, et une seule jointure suffit à les retrouver pour n’importe quel contrat.