Recap - Real Estate Contractor

CoddyのSQLジャーニー「基礎」セクションの一部。レッスン 51/72。

チャレンジ

中級

利用可能なテーブルと列:
real_estate: asset_id, value, date
potential: asset_id, forecast

一部のアナリストは不動産資産の成長率を予測しています。彼らの予測が正しいかどうかを確認したいと思います。

real_estate テーブル内の各資産について、新しい日付の value と古い日付の value の比率を計算してください。

例えば、2022-01-01 の値が 5 で、2022-05-01 の値が 10 である資産がある場合、その比率は 10/5 = 2 となります。

2つの日付の日数差が 101 未満の比較はすべて無視してください。

各資産には多くの日付があるため、比較する比率も多数存在します。各資産について最大の比率のみを保持してください。

最後に、この比率を potential テーブルの対応する forecast で除算し（このフィールドを 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) > ____
    -- グループごとに1つの結果行とし、最大の比較のみを保持する
    GROUP BY r1.asset_id
) AS actual
JOIN potential ON ____
WHERE ratio_forecast > 1
ORDER BY ____

基礎のすべてのレッスン

3Specific Return Format

Null values Sort Results Part 1 Sort Results Part 2 Recap - Cyber Security Firm Limit number of records Recap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary Election Recap - Police Criminal Arrest Recap - Bar Beverage Container Recap - Engineer new columns

9Multiple tables

Basic Join Part 1 Basic Join Part 2 Recap - Join Self join Recap - Self Join Union Simplify queries, WITH keyword Recap - With QueriesRecap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK Functions Recap - RANK & DENSE_RANK NTILE Function Aggregation Functions ROWS & RANGE Criterion

自分で練習してみよう: SQLプレイグラウンド