2.sql

-- Создание таблицы Classes
CREATE TABLE Classes (
	class VARCHAR(100) NOT NULL,
	type VARCHAR(20) NOT NULL CHECK (type IN ('Racing', 'Street')), -- тип класса
	country VARCHAR(100) NOT NULL,
	numDoors INT NOT NULL,
	engineSize DECIMAL(3, 1) NOT NULL, -- размер двигателя в литрах
	weight INT NOT NULL,            	-- вес автомобиля в килограммах
	PRIMARY KEY (class)
);

-- Создание таблицы Cars
CREATE TABLE Cars (
	name VARCHAR(100) NOT NULL,
	class VARCHAR(100) NOT NULL,
	year INT NOT NULL,
	PRIMARY KEY (name),
	FOREIGN KEY (class) REFERENCES Classes(class)
);

-- Создание таблицы Races
CREATE TABLE Races (
	name VARCHAR(100) NOT NULL,
	date DATE NOT NULL,
	PRIMARY KEY (name)
);

-- Создание таблицы Results
CREATE TABLE Results (
	car VARCHAR(100) NOT NULL,
	race VARCHAR(100) NOT NULL,
	position INT NOT NULL,
	PRIMARY KEY (car, race),
	FOREIGN KEY (car) REFERENCES Cars(name),
	FOREIGN KEY (race) REFERENCES Races(name)
);

-- Вставка данных в таблицу Classes
INSERT INTO Classes (class, type, country, numDoors, engineSize, weight) VALUES
('SportsCar', 'Racing', 'USA', 2, 3.5, 1500),
('Sedan', 'Street', 'Germany', 4, 2.0, 1200),
('SUV', 'Street', 'Japan', 4, 2.5, 1800),
('Hatchback', 'Street', 'France', 5, 1.6, 1100),
('Convertible', 'Racing', 'Italy', 2, 3.0, 1300),
('Coupe', 'Street', 'USA', 2, 2.5, 1400),
('Luxury Sedan', 'Street', 'Germany', 4, 3.0, 1600),
('Pickup', 'Street', 'USA', 2, 2.8, 2000);

-- Вставка данных в таблицу Cars
INSERT INTO Cars (name, class, year) VALUES
('Ford Mustang', 'SportsCar', 2020),
('BMW 3 Series', 'Sedan', 2019),
('Toyota RAV4', 'SUV', 2021),
('Renault Clio', 'Hatchback', 2020),
('Ferrari 488', 'Convertible', 2019),
('Chevrolet Camaro', 'Coupe', 2021),
('Mercedes-Benz S-Class', 'Luxury Sedan', 2022),
('Ford F-150', 'Pickup', 2021),
('Audi A4', 'Sedan', 2018),
('Nissan Rogue', 'SUV', 2020);

-- Вставка данных в таблицу Races
INSERT INTO Races (name, date) VALUES
('Indy 500', '2023-05-28'),
('Le Mans', '2023-06-10'),
('Monaco Grand Prix', '2023-05-28'),
('Daytona 500', '2023-02-19'),
('Spa 24 Hours', '2023-07-29'),
('Bathurst 1000', '2023-10-08'),
('Nürburgring 24 Hours', '2023-06-17'),
('Pikes Peak International Hill Climb', '2023-06-25');

-- Вставка данных в таблицу Results
INSERT INTO Results (car, race, position) VALUES
('Ford Mustang', 'Indy 500', 1),
('BMW 3 Series', 'Le Mans', 3),
('Toyota RAV4', 'Monaco Grand Prix', 2),
('Renault Clio', 'Daytona 500', 5),
('Ferrari 488', 'Le Mans', 1),
('Chevrolet Camaro', 'Monaco Grand Prix', 4),
('Mercedes-Benz S-Class', 'Spa 24 Hours', 2),
('Ford F-150', 'Bathurst 1000', 6),
('Audi A4', 'Nürburgring 24 Hours', 8),
('Nissan Rogue', 'Pikes Peak International Hill Climb', 3);

-- Задача 1
WITH car_stats AS (
    SELECT c.name AS car_name, c.class AS car_class, AVG(r.position)::numeric(10,4) AS average_position, COUNT(*) AS race_count
    FROM Cars c
    JOIN Results r ON r.car = c.name
    GROUP BY c.name, c.class
),
class_min AS (
    SELECT car_class, MIN(average_position) AS min_average_position
    FROM car_stats
    GROUP BY car_class
)
SELECT cs.car_name, cs.car_class, cs.average_position, cs.race_count
FROM car_stats cs
JOIN class_min cm ON cm.car_class = cs.car_class AND cm.min_average_position = cs.average_position
ORDER BY cs.average_position, cs.car_name;

-- Задача 2
WITH car_stats AS (
    SELECT c.name AS car_name, c.class AS car_class, cl.country, AVG(r.position)::numeric(10,4) AS average_position, COUNT(*) AS race_count
    FROM Cars c
    JOIN Classes cl ON cl.class = c.class
    JOIN Results r ON r.car = c.name
    GROUP BY c.name, c.class, cl.country
)
SELECT car_name, car_class, average_position, race_count, country
FROM car_stats
ORDER BY average_position, car_name
LIMIT 1;

-- Задача 3
WITH car_stats AS (
    SELECT c.name AS car_name, c.class AS car_class, cl.country, AVG(r.position)::numeric(10,4) AS average_position, COUNT(*) AS race_count
    FROM Cars c
    JOIN Classes cl ON cl.class = c.class
    JOIN Results r ON r.car = c.name
    GROUP BY c.name, c.class, cl.country
),
class_stats AS (
    SELECT car_class, MIN(average_position) AS class_min_avg
    FROM car_stats
    GROUP BY car_class
),
global_min_class AS (
    SELECT MIN(class_min_avg) AS min_of_class_mins
    FROM class_stats
),
selected_classes AS (
    SELECT cs.car_class
    FROM class_stats cs
    CROSS JOIN global_min_class g
    WHERE cs.class_min_avg = g.min_of_class_mins
),
class_races AS (
    SELECT c.class AS car_class, COUNT(*) AS total_races_in_class
    FROM Cars c
    JOIN Results r ON r.car = c.name
    GROUP BY c.class
)
SELECT s.car_name, s.car_class, s.average_position, s.race_count, s.country, cr.total_races_in_class
FROM car_stats s
JOIN selected_classes sc ON sc.car_class = s.car_class
JOIN class_races cr ON cr.car_class = s.car_class
ORDER BY s.average_position, s.car_name;

-- Задача 4
WITH car_stats AS (
    SELECT c.name AS car_name, c.class AS car_class, cl.country, AVG(r.position)::numeric(10,4) AS average_position, COUNT(*) AS race_count, COUNT(*) OVER (PARTITION BY c.class) AS cars_in_class
    FROM Cars c
    JOIN Classes cl ON cl.class = c.class
    JOIN Results r ON r.car = c.name
    GROUP BY c.name, c.class, cl.country
),
class_avg AS (
    SELECT car_class, AVG(average_position) AS class_average_position, MAX(cars_in_class) AS cars_in_class
    FROM car_stats
    GROUP BY car_class
)
SELECT cs.car_name, cs.car_class, cs.average_position, cs.race_count, cs.country
FROM car_stats cs
JOIN class_avg ca ON ca.car_class = cs.car_class
WHERE ca.cars_in_class >= 2 AND cs.average_position < ca.class_average_position
ORDER BY cs.car_class, cs.average_position;

-- Задача 5
WITH car_stats AS (
    SELECT c.name AS car_name, c.class AS car_class, ROUND(AVG(r.position), 4) AS average_position, COUNT(r.race) AS race_count
    FROM Cars c
    JOIN Results r ON r.car = c.name
    GROUP BY c.name, c.class
),
class_car_counts AS (
    SELECT class, COUNT(*) AS low_position_count
    FROM Cars
    GROUP BY class
),
class_race_counts AS (
    SELECT c.class, COUNT(DISTINCT r.race) AS total_races
    FROM Cars c
    LEFT JOIN Results r ON r.car = c.name
    GROUP BY c.class
),
selected_classes AS (
    SELECT cs.car_class
    FROM car_stats cs
    WHERE cs.average_position > 3.0
    GROUP BY cs.car_class
    HAVING COUNT(*) = (
        SELECT MAX(cnt)
        FROM (
            SELECT COUNT(*) AS cnt
            FROM car_stats
            WHERE average_position > 3.0
            GROUP BY car_class
        ) t
    )
)
SELECT cs.car_name, cs.car_class, cs.average_position, cs.race_count, cl.country AS car_country, crc.total_races, ccc.low_position_count
FROM car_stats cs
JOIN selected_classes sc ON sc.car_class = cs.car_class
JOIN Classes cl ON cl.class = cs.car_class
JOIN class_race_counts crc ON crc.class = cs.car_class
JOIN class_car_counts ccc ON ccc.class = cs.car_class
WHERE cs.average_position > 3.0
ORDER BY ccc.low_position_count DESC;