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

Часть раздела Основы путешествия по 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 дня.

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

Наконец, разделите это отношение на соответствующий forecast из таблицы 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 ____