2e.txt

-- Ερώτημα 2e
-- 1η παραδοχή: Θεωρούμε ότι δεν υπάρχουν αυτοκίνητα που κυκλοφόρησαν πρώτη φορά πριν τον 1980
-- 2η παραδοχή: Όσα οχήματα υπάρχουν, τόσα είναι και τα διαφορετικά συμβόλαια
WITH contracts_0_4 (first_year,count_contracts) AS
(
SELECT first_year, COUNT(DISTINCT plate_number) AS count_contracts 
FROM vehicles
WHERE (SELECT date_part('year',CURRENT_DATE))-first_year <= 4
GROUP BY first_year
)
,
contracts_5_9 (first_year,count_contracts) AS
(
SELECT first_year, COUNT(DISTINCT plate_number) AS count_contracts
FROM vehicles
WHERE (SELECT date_part('year',CURRENT_DATE))-first_year <= 9 AND (SELECT date_part('year',CURRENT_DATE))-first_year >= 5
GROUP BY first_year
)
,
contracts_10_19 (first_year,count_contracts) AS
(
SELECT first_year, COUNT(DISTINCT plate_number) AS count_contracts
FROM vehicles
WHERE (SELECT date_part('year',CURRENT_DATE))-first_year <= 19 AND (SELECT date_part('year',CURRENT_DATE))-first_year >= 10
GROUP BY first_year
)
,
contracts_20plus (first_year,count_contracts) AS
(
SELECT first_year, COUNT(DISTINCT plate_number) AS count_contracts
FROM vehicles
WHERE (SELECT date_part('year',CURRENT_DATE))-first_year >=20
GROUP BY first_year
)

(SELECT '0-4' AS group,CAST(SUM(count_contracts)/5 AS DECIMAL(10,2)) AS average_contracts
FROM contracts_0_4)
UNION
(SELECT '5-9' AS group,CAST(SUM(count_contracts)/5 AS DECIMAL(10,2)) AS average_contracts
FROM contracts_5_9)
UNION
(SELECT '10-19' AS group,CAST(SUM(count_contracts)/10 AS DECIMAL(10,2)) AS average_contracts
FROM contracts_10_19)
UNION
(SELECT '20+' AS group,CAST(SUM(count_contracts)/((SELECT date_part('year',CURRENT_DATE))-1999) AS DECIMAL(10,2)) AS average_contracts
FROM contracts_20plus)