Повторение - Подрядчик по недвижимости

Часть раздела Основы путешествия по SQL на Coddy. Урок 51 из 72.

Задание

Средне

Доступные таблицы и столбцы:
real_estate: asset_id, value, date
potential: asset_id, forecast

Некоторые аналитики прогнозируют рост стоимости активов недвижимости. Мы хотим проверить, верен ли их прогноз.

Для каждого актива в таблице real_estate вычислите отношение значения value для более новой даты к значению для более старой даты.

Например, если значение актива на 2022-01-01 было равно 5, а на 2022-05-01 — 10, то это отношение равно 10/5 = 2.

Пропустите все сравнения, в которых разница между двумя датами меньше 101 дня.

Для каждого актива есть много дат, поэтому существует много отношений для сравнения. Оставьте только наибольшее отношение для каждого актива.

Наконец, разделите это отношение на соответствующий прогноз из таблицы potential (назовите это поле ratio_forecast) и отсортируйте результаты в порядке убывания.

Итоговый результат должен содержать asset_id и ratio_forecast.

Оставьте только те активы, у которых значение ratio_forecast больше 1.

Примечание: Чтобы получить результат с десятичной частью при операции деления (/), иногда необходимо умножить числитель на 1.0.

Например, вместо a / b используйте a * 1.0 / b, чтобы получить результат с десятичной частью (если a и b — переменные)

Попробуйте сами

-- Внешний запрос: разделить число, вычисленное ниже, на соответствующее из другой таблицы
SELECT potential.asset_id, ____ AS ratio_forecast
FROM (
    -- Внутренний запрос: соединить таблицу саму с собой, чтобы более старую строку можно было сравнить с более новой
    SELECT r1.asset_id, ____ AS ratio
    FROM real_estate AS r1
    JOIN real_estate AS r2 ON ____
    -- Оставить только пары, достаточно удалённые друг от друга во времени
    WHERE JULIANDAY(r2.date) - JULIANDAY(r1.date) > ____
    -- Одна результирующая строка на группу, оставляя только одно наибольшее сравнение
    GROUP BY r1.asset_id
) AS actual
JOIN potential ON ____
WHERE ratio_forecast > 1
ORDER BY ____