PROJET DATAImmo  
Analyse des données  
à la demande de François LAPEYRE  
1
-- Piece principale > 9m2 et hauteur > 1.80m  
-- Archivage des biens mal renseignés (ex: 8 pièces principales pour 23m2  
| 0 pièce principale | Valeur foncière nulle)  
CREATE VIEW biens_mal_renseignes AS  
SELECT * FROM bien  
JOIN vente  
ON (bien.Id_bien = vente.Id_vente)  
WHERE (Piece_principale*9) > bien.Surface_carrez  
OR Piece_principale = 0  
OR Valeur_fonciere = 0;  
/*  
Dans le cadre professionel et hors exercice, j'aurai supprimé ces  
individus qui fausses les statistiques :  
-- Suppression des biens concernés dans vente  
DELETE FROM vente  
WHERE Id_vente IN  
(
SELECT Id_bien  
FROM bien  
WHERE (Piece_principale * 9) > Surface_carrez  
OR Piece_principale = 0  
)
OR Valeur_fonciere = 0;  
-- Suppression des biens concernés dans bien  
DELETE FROM bien  
WHERE (Piece_principale * 9) > Surface_carrez  
OR Piece_principale = 0  
OR Id_bien NOT IN  
(
SELECT Id_vente FROM vente  
);  
*/  
2
-- 1. Nombre total d’appartements vendus au 1er semestre 2020.  
SELECT COUNT(Id_vente) AS "Appartements vendus (1er semestre)"  
FROM vente  
JOIN bien ON (bien.Id_bien = vente.Id_vente )  
WHERE Date_mutation >= DATE("2020-01-01")  
AND Date_mutation <= DATE("2020-06-31")  
AND Type_local = "Appartement";  
RESULTAT :  
Ventes_1er_semestre  
31378  
3
-- 2. Le nombre de ventes d’appartement par région pour le 1er semestre  
2020.  
SELECT Nom_region,  
COUNT(Id_vente) AS Nb_bien_vendus  
FROM bien  
JOIN vente ON (bien.Id_bien = vente.Id_vente )  
JOIN commune USING (Codedep_codecommune)  
JOIN region USING (Code_region)  
WHERE Date_mutation >= DATE("2020-01-01")  
AND Date_mutation <= DATE("2020-06-30")  
AND Type_local = "Appartement"  
GROUP BY Code_region  
ORDER BY Nb_bien_vendus DESC;  
RESULTAT :  
Nom_region  
Nb_bien_vendus  
Ile-de-France  
Provence-Alpes-Côte d'Azur  
Auvergne-Rhône-Alpes  
Nouvelle-Aquitaine  
Occitanie  
13995  
3649  
3253  
1932  
1640  
1357  
1254  
984  
983  
862  
696  
376  
223  
94  
Pays de la Loire  
Hauts-de-France  
Grand Est  
Bretagne  
Normandie  
Centre-Val de Loire  
Bourgogne-Franche-Comté  
Corse  
Martinique  
La Réunion  
44  
Guyane  
34  
Guadeloupe  
2
4
-- 3. Proportion des ventes d’appartements par le nombre de pièces.  
SELECT Piece_principale,  
ROUND  
(
COUNT() *  
100.0 /  
(
SELECT COUNT()  
FROM vente  
JOIN bien ON (Id_bien = Id_vente)  
WHERE Type_local = "Appartement"  
)
,2) AS "Ratio (%)"  
FROM vente  
JOIN bien ON (bien.Id_bien = vente.Id_vente )  
WHERE Type_local = "Appartement"  
GROUP BY Piece_principale;  
RESULTAT :  
Piece_principale  
Ratio (%)  
0
1
0.1  
21.48  
31.18  
28.57  
14.21  
3.55  
0.65  
0.17  
0.05  
0.03  
0.01  
0
2
3
4
5
6
7
8
9
10  
11  
5
-- 4. Liste des 10 départements où le prix du mètre carré est le plus élevé.  
SELECT SUBSTR(Codedep_codecommune, 1, 2) AS Departement  
ROUND(AVG(Valeur_fonciere / Surface_carrez),2) AS Valeur_surface,  
FROM bien  
JOIN vente ON (Id_vente = Id_bien)  
JOIN commune USING (Codedep_codecommune)  
GROUP BY Departement  
ORDER BY Valeur_surface DESC  
LIMIT 10;  
RESULTAT :  
Departement Valeur_surface  
75  
92  
94  
6
12045.34  
7219.38  
5340.5  
4696.76  
4667.11  
4336.68  
4225.23  
4059.26  
4010.59  
3764.13  
74  
93  
78  
69  
2A  
33  
6
-- 5. Prix moyen du mètre carré d’une maison en Île-de-France.  
SELECT Nom_region,  
ROUND(AVG(Valeur_fonciere / Surface_carrez),2) AS ‘Prix moyen surface maison’  
FROM bien  
JOIN vente ON (Id_vente = Id_bien)  
JOIN commune USING (Codedep_codecommune)  
JOIN region USING (Code_region)  
WHERE Code_region = 11  
AND Type_local = "Maison";  
RESULTAT :  
Nom_region  
Prix_moyen_surface_maison  
3745.05  
Ile-de-France  
7
-- 6. Liste des 10 appartements les plus chers avec la région et le  
nombre de mètres carrés.  
SELECT Valeur_fonciere,  
Surface_carrez,  
Nom_region,  
Code_region,  
FROM bien  
JOIN vente ON (Id_vente = Id_bien)  
JOIN commune USING (Codedep_codecommune)  
JOIN region USING (Code_region)  
WHERE Type_local = "Appartement"  
ORDER BY Valeur_fonciere DESC  
LIMIT 10;  
RESULTAT :  
Valeur_fonciere Surface_carrez Nom_region  
Code_region  
9000000  
8600000  
8577713  
7620000  
7600000  
7535000  
7420000  
7200000  
7050000  
6600000  
9.1  
Ile-de-France 11  
64  
Ile-de-France 11  
Ile-de-France 11  
Ile-de-France 11  
Ile-de-France 11  
Ile-de-France 11  
Ile-de-France 11  
Ile-de-France 11  
Ile-de-France 11  
Ile-de-France 11  
20.55  
42.77  
253.3  
139.9  
360.95  
595  
122.56  
79.38  
8
-- 7. Taux d’évolution du nombre de ventes entre le premier et le second  
trimestre de 2020.  
WITH nbventeT1 AS  
(
SELECT CAST(COUNT() AS REAL) AS Nb_de_venteT1  
FROM vente  
WHERE Date_mutation >= DATE("2020-01-01")  
AND Date_mutation <= DATE("2020-03-31")  
),  
nbventeT2 AS  
(
SELECT CAST(COUNT() AS REAL) AS Nb_de_venteT2  
FROM vente  
WHERE Date_mutation >= DATE("2020-04-01")  
AND Date_mutation <= DATE("2020-06-30")  
)
SELECT ROUND((Nb_de_venteT2 - Nb_de_venteT1) / Nb_de_venteT1 * 100.0,2)  
AS "Taux d'evolution (%)"  
FROM nbventeT1,  
nbventeT2;  
RESULTAT :  
Taux d'evolution (%)  
3.68  
9
-- 8. Le classement des régions par rapport au prix au mètre carré des  
appartement de plus de 4 pièces.  
SELECT RANK() OVER(ORDER BY AVG(Valeur_fonciere / Surface_carrez)DESC)  
AS Classement,  
Code_region,  
Nom_region,  
CAST(AVG(Valeur_fonciere / Surface_carrez) AS INTEGER) AS Prix_M2  
FROM vente  
JOIN bien ON (Id_vente = Id_bien)  
JOIN commune USING (Codedep_codecommune)  
JOIN region USING (Code_region)  
WHERE Piece_principale > 4  
AND Type_local = "Appartement"  
AND Valeur_fonciere != 0  
AND Surface_carrez != 0  
GROUP BY Code_region  
ORDER BY Prix_M2 DESC;  
RESULTAT :  
Classement Code_region Nom_region  
Prix_M2  
8770  
1
11  
4
Ile-de-France  
La Réunion  
2
3641  
3
93  
94  
84  
75  
53  
52  
32  
76  
28  
44  
24  
27  
2
Provence-Alpes-Côte d'Azur 3587  
4
Corse  
3104  
2891  
2465  
2412  
2315  
2189  
2097  
2015  
1540  
1453  
1251  
573  
5
Auvergne-Rhône-Alpes  
Nouvelle-Aquitaine  
Bretagne  
6
7
8
Pays de la Loire  
Hauts-de-France  
Occitanie  
9
10  
11  
12  
13  
14  
15  
Normandie  
Grand Est  
Centre-Val de Loire  
Bourgogne-Franche-Comté  
Martinique  
10  
-- 9. Liste des communes ayant eu au moins 50 ventes au 1er trimestre  
SELECT Codedep_codecommune AS Code_commune,  
Nom_commune,  
COUNT(Id_vente) AS "Nb ventes 1er trimestre"  
FROM commune  
JOIN bien USING (Codedep_codecommune)  
JOIN vente ON (Id_bien = Id_vente)  
WHERE Date_mutation >= DATE("2020-01-01")  
AND Date_mutation <= DATE("2020-03-31")  
GROUP BY Code_commune  
HAVING COUNT() >= 50;  
RESULTAT :  
Code_commune Nom_commune  
Nb ventes 1er trimestre  
06004  
06088  
13028  
13201  
13204  
13208  
13209  
2A004  
30189  
31555  
33063  
34301  
35238  
38185  
44109  
49007  
59350  
75102  
75103  
75104  
75105  
75106  
75107  
75108  
75109  
75110  
75111  
75112  
75113  
75114  
75115  
75116  
Antibes  
77  
Nice  
173  
62  
La Ciotat  
Marseille 1er  
Marseille 4e  
Marseille 8e  
Marseille 9e  
Ajaccio  
71  
72  
81  
66  
54  
Nîmes  
63  
Toulouse  
Bordeaux  
Sète  
78  
157  
62  
Rennes  
61  
Grenoble  
Nantes  
106  
119  
64  
Angers  
Lille  
67  
Paris 2e  
Paris 3e  
Paris 4e  
Paris 5e  
Paris 6e  
Paris 7e  
Paris 8e  
Paris 9e  
Paris 10e  
Paris 11e  
Paris 12e  
Paris 13e  
Paris 14e  
Paris 15e  
Paris 16e  
61  
79  
60  
79  
86  
87  
62  
106  
109  
169  
110  
94  
146  
215  
165  
11  
75117  
75118  
75119  
75120  
78646  
83137  
92004  
92012  
92026  
92040  
92044  
92062  
92063  
93048  
94068  
94080  
Paris 17e  
228  
209  
116  
127  
54  
Paris 18e  
Paris 19e  
Paris 20e  
Versailles  
Toulon  
59  
Asnières-sur-Seine  
81  
Boulogne-Billancourt 99  
Courbevoie  
80  
50  
59  
53  
68  
65  
Issy-les-Moulineaux  
Levallois-Perret  
Puteaux  
Rueil-Malmaison  
Montreuil  
Saint-Maur-des-Fossés 56  
Vincennes 68  
12  
-- 10. Différence en pourcentage du prix au mètre carré entre un  
appartement de 2 pièces et un appartement de 3 pièces.  
WITH AppartementP3 AS  
(
SELECT AVG(Valeur_fonciere / Surface_carrez) AS Prixm2_moyP3  
FROM bien  
JOIN vente  
ON (Id_vente = Id_bien)  
WHERE Piece_principale = 3  
AND Type_local = "Appartement"  
AND Valeur_fonciere != 0  
AND Surface_carrez != 0  
),  
AppartementP2 AS  
(
SELECT AVG(Valeur_fonciere / Surface_carrez) AS Prixm2_moyP2  
FROM bien  
JOIN vente  
ON (Id_vente = Id_bien)  
WHERE Piece_principale = 2  
AND Type_local = "Appartement"  
AND Valeur_fonciere != 0  
AND Surface_carrez != 0  
)
SELECT ROUND((Prixm2_moyP3 - Prixm2_moyP2) / Prixm2_moyP2 * 100 ,2)  
AS "Difference prix M2 (en %)"  
FROM AppartementP3,  
AppartementP2;  
RESULTAT :  
Difference prix M2 (en %)  
-12.4  
13  
-- 11. Les moyennes de valeurs foncières pour le top 3 des communes des  
départements 6, 13, 33, 59 et 69.  
SELECT Departement,  
Classement,  
Moyenne_valeur_financiere,  
Codedep_codecommune AS "Code commune"  
Nom_commune  
FROM (  
SELECT SUBSTR(Codedep_codecommune, 1, 2) AS Departement,  
Codedep_codecommune,  
Nom_commune,  
CAST(AVG(Valeur_fonciere) AS INTEGER) AS Moyenne_valeur_financiere,  
RANK() OVER(PARTITION BY SUBSTR(Codedep_codecommune, 1, 2)  
ORDER BY AVG(Valeur_fonciere) DESC) AS Classement  
FROM vente  
JOIN bien ON (Id_vente = Id_bien)  
JOIN commune USING (Codedep_codecommune)  
WHERE Departement IN ('06', '13', '33', '59', '69')  
GROUP BY Codedep_codecommune  
)
WHERE Classement <= 3  
ORDER BY Departement ASC, Moyenne_valeur_financiere DESC;  
RESULTAT :  
Departement Classement Moyenne_  
Code  
Nom_commune  
valeur_financiere commune  
06  
06  
06  
13  
13  
13  
33  
33  
33  
59  
59  
59  
69  
69  
69  
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
968750  
655000  
476898  
330000  
314425  
313416  
549500  
335000  
307435  
433202  
408550  
322250  
485300  
455217  
426968  
6121  
Saint-Jean-Cap-Ferrat  
Eze  
6059  
6084  
Mouans-Sartoux  
Gignac-la-Nerthe  
Saint-Savournin  
Cassis  
13043  
13101  
13022  
33236  
33539  
33009  
59071  
59168  
59279  
69265  
69382  
69386  
Lège-Cap-Ferret  
Vayres  
Arcachon  
Bersée  
Cysoing  
Halluin  
Ville-sur-Jarnioux  
Lyon 2e  
Lyon 6e  
14  
-- 12. Les 20 communes avec le plus de transactions pour 1000 habitants  
pour les communes qui dépassent les 10 000 habitants.  
SELECT Codedep_codecommune AS "Code commune",  
Nom_commune,  
ROUND  
(
COUNT(Id_vente)/((Pop_municipale + Pop_apart) / 1000.0)  
,2) AS ’Ventes pour 1000 habitants’  
FROM vente  
JOIN bien ON (Id_vente = Id_bien)  
JOIN commune USING (Codedep_codecommune)  
WHERE (Pop_municipale + Pop_apart) >= 10000  
GROUP BY Codedep_codecommune  
ORDER BY ’Ventes pour 1000 habitants’DESC  
LIMIT 20;  
RESULTAT :  
Code  
commune  
75102  
Nom_commune  
Ventes pour  
1000 habitants  
5.84  
Paris 2e  
75101  
75103  
33009  
44055  
75104  
6104  
Paris 1er  
Paris 3e  
4.92  
4.69  
4.62  
4.58  
4.08  
Arcachon  
La Baule-Escoublac  
Paris 4e  
Roquebrune-Cap-Martin 3.99  
75108  
83123  
83071  
75109  
75106  
83112  
60141  
94067  
44132  
75110  
6083  
Paris 8e  
3.83  
3.5  
Sanary-sur-Mer  
La Londe-les-Maures  
Paris 9e  
3.43  
3.43  
3.38  
3.24  
3.13  
3.06  
3.06  
3.04  
2.94  
Paris 6e  
Saint-Cyr-sur-Mer  
Chantilly  
Saint-Mandé  
Pornichet  
Paris 10e  
Menton  
85226  
94080  
Saint-Hilaire-de-Riez 2.87  
Vincennes 2.81  
15