|
|
|
|
|
|
Query
-- Overdue mortgage statistics by year with tenure
SELECT Mortgage_Type,
Mortgage_Tenure,
AVG(Days_Overdue) AS Avg_Days,
AVG(Outstanding_Amount) AS Avg_Amount,
COUNT(*) AS Record_Count
FROM CreditHistory_Fact A,
Mortgage_Dimension B,
Date_Dimension C
WHERE A.Mortgage_Key = B.Mortgage_Key
AND A.Date_Key = C.Date_Key
GROUP BY Calendar_Year,Mortgage_Type,Mortgage_Tenure
ORDER BY Calendar_Year,
Mortgage_Type,
Mortgage_Tenure;
Example
Mortgage_Type | Mortgage_Tenure | Avg_Days | Avg_Amount | Record_Count
--------------+-----------------+------------------+------------------+------------
Car | 12 | 498.664561695056 | 7745.60994349813 | 24070
Car | 24 | 502.332021237642 | 7753.32524533138 | 21848
Car | 36 | 500.580798992262 | 7793.29573420911 | 22228
Car | 48 | 498.262124831239 | 7730.3517000727 | 19258
Car | 60 | 495.317695109836 | 7768.80918644442 | 18118
Home | 60 | 500.719860896445 | 7858.66575637558 | 20704
Home | 96 | 500.386262760763 | 7710.50094429649 | 18024
Home | 120 | 496.92023054755 | 7751.82940172911 | 17350
Home | 180 | 498.150733659404 | 7721.11076144953 | 17992
Home | 240 | 501.653077224618 | 7662.79473049784 | 20408
(10 rows)