مراجعة - مقاول العقارات

جزء من قسم الأساسيات في رحلة 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 ____