P3_script Requêtes.txt

1. Nombre total d’appartements vendus au 1er semestre 2020.

SELECT count(DISTINCT id_vente) AS nb_appart_S1_2020 FROM vente
JOIN bien USING(id_bien)
WHERE date_signature BETWEEN '2020-01-01' AND '2020-06-30' AND type_local='Appartement';



R2:
SELECT nom_region, COUNT(DISTINCT id_vente) AS nb_appart_S1_2020 FROM vente
JOIN bien USING(id_bien)
JOIN commune USING(id_commune)
JOIN departement USING(code_departement)
WHERE date_signature BETWEEN '2020-01-01' AND '2020-06-30' AND type_local='Appartement'
GROUP BY nom_region;

R3:
SELECT total_piece AS nb_pieces,
count(*) AS nb_ventes,
ROUND(count(*)*100/(SELECT count(DISTINCT id_vente) FROM vente JOIN bien USING (id_bien) WHERE type_local = 'Appartement'),2) 
AS proportion FROM vente
JOIN bien USING(id_bien) 
WHERE type_local='Appartement' 
GROUP BY nb_pieces 
ORDER BY nb_pieces ASC;


R4:
SELECT code_departement, ROUND(AVG(montant/surface_carrez),2) AS prix_m2
FROM bien 
JOIN vente USING (id_bien)
JOIN commune USING(id_commune)
WHERE surface_carrez > 0
GROUP BY code_departement
ORDER BY prix_m2 DESC
LIMIT 10;

R5:
SELECT nom_region, ROUND(AVG(montant/surface_carrez),2) AS prix_m2
FROM bien 
JOIN vente USING (id_bien)
JOIN commune USING(id_commune)
JOIN departement USING (code_departement)
WHERE type_local='Maison' AND nom_region='Île-de-France' AND surface_carrez > 0;


R6:
SELECT id_bien, montant, nom_region, surface_carrez FROM bien
JOIN vente USING (id_bien)
JOIN commune USING (id_commune)
JOIN departement USING (code_departement)
WHERE type_local = 'Appartement'
ORDER BY montant DESC
LIMIT 10;

R7:
WITH venteT1 AS (
SELECT COUNT(DISTINCT id_vente) AS nbventeT1 FROM vente
JOIN bien USING (id_bien)
WHERE date_signature BETWEEN '2020-01-01' AND '2020-03-31' AND type_local='Appartement'
),
venteT2 AS (
SELECT COUNT(DISTINCT id_vente) AS nbventeT2 FROM vente
JOIN bien USING (id_bien)
WHERE date_signature BETWEEN '2020-04-01' AND '2020-06-30' AND type_local='Appartement'
)
SELECT ROUND((nbventeT2-nbventeT1)*100/nbventeT1,2) AS evolution FROM venteT1, venteT2;


R8:
SELECT ROUND(AVG(montant/surface_carrez),2) AS prix_m2, nom_region
FROM bien 
JOIN vente USING (id_bien)
JOIN commune USING(id_commune)
JOIN departement USING(code_departement)
WHERE type_local='Appartement' AND total_piece>4 AND surface_carrez>0
GROUP BY nom_region
ORDER BY prix_m2 DESC;


R9:
SELECT nom_commune, COUNT(DISTINCT id_vente)AS nb_vente_T1 FROM vente
JOIN bien USING(id_bien)
JOIN commune USING(id_commune)
WHERE date_signature BETWEEN '2020-01-01' AND '2020-03-31'
GROUP BY nom_commune
HAVING count(DISTINCT id_vente)>=50;

R10:
WITH table1 AS(
SELECT AVG(montant/surface_carrez) AS T2_prix_m2
FROM bien 
JOIN vente USING (id_bien)
WHERE type_local='Appartement' AND total_piece=2 AND surface_carrez>0
),
table2 AS (
SELECT AVG(montant/surface_carrez) AS T3_prix_m2
FROM bien 
JOIN vente USING (id_bien)
WHERE type_local='Appartement' AND total_piece=3 AND surface_carrez>0
)
SELECT ROUND((T3_prix_m2-T2_prix_m2)*100/T2_prix_m2,2) as difference FROM table1, table2;

WITH table1 AS(
	SELECT 
	code_departement,
	id_commune,
	nom_commune,
	ROUND(AVG(montant),2) AS moyenne_val_fonciere ,
	rank() OVER (PARTITION BY code_departement ORDER BY AVG(montant) DESC) classement
	FROM vente
	JOIN bien USING (id_bien)
	JOIN commune USING (id_commune)
	WHERE code_departement IN ('06','13','33','59','69')
	GROUP BY id_commune)
SELECT * FROM table1
WHERE table1.classement <=3;