סיכום – קבלן נדל״ן

חלק מהיחידה יסודות במסלול ה-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 ____