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

جزء من قسم الأساسيات في رحلة SQL على Coddy. الدرس 51 من 72.

التحدي

متوسط

الجداول والأعمدة المتاحة:
real_estate: asset_id، value، date
potential: asset_id، forecast

يتوقع بعض المحللين مقدار النمو الذي ستحققه أصول real_estate. نريد التحقق مما إذا كان توقعهم صحيحًا.

لكل أصل في جدول real_estate، احسب ratio بين value في تاريخ newer وقيمة تاريخ older.

على سبيل المثال، إذا كانت قيمة أحد الأصول في 2022-01-01 تساوي 5 وفي 2022-05-01 تساوي 10، فإن ratio يساوي 10/5 = 2.

تجاهل كل المقارنات التي يكون فيها فرق الأيام بين التاريخين أصغر من 101.

هناك تواريخ عديدة لكل أصل، ولذلك توجد ratios عديدة للمقارنة. احتفظ بـ أكبر ratio لكل أصل.

أخيرًا، نفّذ divide لهذا ratio على forecast المطابق من جدول potential (وسمِّ هذا الحقل ratio_forecast)، ثم رتّب result ترتيبًا تنازليًا.

يجب أن يحتوي result النهائي على asset_id وratio_forecast.

احتفظ only بالأصول التي يكون فيها ratio_forecast أكبر من 1.

ملاحظة: للحصول على نتيجة عشرية من عملية divide (/)، قد تحتاج أحيانًا إلى ضرب البسط في 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 ____