Insurance – ESN Data (Openclassroom)
SQL Practice : Housing Insurance
Fictional
|
14/12/2024 > 30/12/2024
Context
I completed this project for ESN Data (fictional) as part of my Business Intelligence Analyst training. The goal was to create and manage a relational database to analyze the French housing insurance market. I helped a fictional insurance company structure its data, load a dataset of contracts and a geographical reference file, and write SQL queries to extract key indicators.
Datasets
- CONTRAT.CSV – 30,335 housing insurance contracts (11 attributes)
- REGION.CSV – 38,916 department-city pairs (7 attributes)
Workflow
- Exploration and data dictionary in Excel (types, constraints, descriptions)
- UML modeling, then relational schema via SQL Power Architect
- CSV import into MySQL (inconsistencies cleaned via VLOOKUP)
- Wrote 12 SQL queries (SELECT, JOIN, aggregations, ORDER BY, AVG/COUNT functions)
Insights
- Top 5 contracts with the largest surface area: between 559 m² and 815 m²
- Average monthly premium: €19/month
- Declared value distribution: mostly under €25,000; only 104 contracts over €100,000
- “Integral” plans in Pays de la Loire: 589 occurrences
- Houses in department 71: 48 contracts, evenly split between Classic and Integral plans
- Average surface area in Paris: 52 m²
- Top 10 departments by average premium: Paris (€36), Hauts-de-Seine (€26), Val-de-Marne (€20) etc.
- Most covered city: Paris
- Most active region: Île-de-France (14,177 contracts)
These results highlight the strong concentration of contracts in Île-de-France, disparities in premium amounts across regions, and the profiles of insured properties.
Business Impact
Through this project, I developed hands-on skills in database design and SQL querying—essential for any BI Analyst. The insights can help the insurer:
- Prioritize tailored offers in high-premium areas (e.g. Paris)
- Adjust insurance plans based on declared value distribution
- Target prospecting campaigns in under-covered cities
Internally, these deliverables (schema, SQL code, screenshots) serve as a reference for future analyses and support data-driven decision-making.