---

SELECT
    year_vipuska AS 'Год выпуска',
    id_car AS 'ID автомобиля',
    marka AS 'Марка',
    model AS 'Модель',
    review_count AS 'Количество отзывов'
FROM (
    SELECT
        car_reviews.*,
        ROW_NUMBER() OVER (
            PARTITION BY year_vipuska
            ORDER BY review_count DESC, id_car
        ) AS position_in_year
    FROM (
        SELECT
            YEAR(c.data_vipuska) AS year_vipuska,
            c.id_car,
            c.marka,
            c.model,
            COUNT(o.id_otziv) AS review_count
        FROM car c
        LEFT JOIN otziv o
            ON c.id_car = o.id_car
        GROUP BY
            c.id_car,
            YEAR(c.data_vipuska),
            c.marka,
            c.model
    ) AS car_reviews
) AS ranked_cars
WHERE position_in_year <= 3
ORDER BY year_vipuska, position_in_year;