|
|
|
|
|
|
Query
-- Overdue statistics for 2001 by Institution
-- a. Avg Overdue (Amount and Days)
-- b. Max Overdue (Amount and Days)
-- c. Min Overdue (Amount and Days)
SELECT Institution_Name,
MAX(Days_Overdue) AS Max_Days,
MIN(Days_Overdue) AS Min_Days,
AVG(Days_Overdue) AS Avg_Days,
MAX(Outstanding_Amount) AS Max_Amount,
MIN(Outstanding_Amount) AS Min_Amount,
AVG(Outstanding_Amount) AS Avg_Amount,
COUNT(*) AS Overdue_Recs
FROM CreditHistory_Fact A,
Institution_Dimension B,
Date_Dimension C
WHERE A.Date_Key = C.Date_Key
AND A.Institution_Key = B.Institution_Key
AND C.Calendar_Year = 2000
GROUP BY Institution_Name
ORDER BY Avg_Amount DESC;
Example
Institution_Name | Max_Days | Min_Days | Avg_Days | Max_Amount | Min_Amount | Avg_Amount | Overdue_Recs
------------------+----------+----------+------------------+------------+------------+------------------+--------------
INSTT#98 | 997 | 0 | 506.386450381679 | 14986.93 | 511.55 | 8034.51529580153 | 1048
INSTT#57 | 999 | 2 | 494.70480081716 | 15000.01 | 508.57 | 8023.94215526047 | 979
INSTT#83 | 999 | 0 | 508.528806584362 | 14994.48 | 502.39 | 8019.49127572016 | 972
INSTT#56 | 999 | 3 | 516.19877675841 | 14979.93 | 511.46 | 7998.86175331295 | 981
INSTT#45 | 997 | 1 | 498.116596638655 | 14994.69 | 507.47 | 7985.12201680672 | 952
INSTT#66 | 998 | 0 | 488.579420579421 | 14990.66 | 501.3 | 7973.51433566434 | 1001
INSTT#84 | 998 | 0 | 505.276302851524 | 14985.32 | 504.76 | 7964.23406096362 | 1017
INSTT#90 | 996 | 1 | 510.30303030303 | 14990.34 | 536.82 | 7951.99204301075 | 1023
INSTT#44 | 998 | 0 | 484.883883883884 | 14970.27 | 525.28 | 7945.75424424424 | 999
INSTT#69 | 999 | 2 | 507.625502008032 | 14986.1 | 509.98 | 7936.75596385542 | 996
INSTT#93 | 998 | 0 | 502.520669291339 | 15000.51 | 502.02 | 7936.17729330709 | 1016
INSTT#73 | 998 | 0 | 491.066198595787 | 14993.14 | 559.45 | 7924.45994984955 | 997