|
|
|
|
|
|
Query
-- Overdue statistics for 2001 by state
-- a. Avg Overdue (Amount and Days)
-- b. Max Overdue (Amount and Days)
-- c. Min Overdue (Amount and Days)
SELECT State,
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,
Customer_Dimension B,
Date_Dimension C
WHERE A.Date_Key = C.Date_Key
AND A.Customer_Key = B.Customer_Key
AND C.Calendar_Year = 2001
GROUP BY State
ORDER BY Avg_Amount DESC,
Avg_Days DESC;
Example
State | Max_Days | Min_Days | Avg_Days | Max_Amount | Min_Amount | Avg_Amount | Overdue_Recs
-------+----------+----------+------------------+------------+------------+------------------+--------------
IL | 999 | 0 | 498.137946406459 | 15000.3 | 500.67 | 7785.36343702016 | 20189
NY | 999 | 0 | 500.163568584688 | 15000.11 | 500.02 | 7750.80704536809 | 39433
CA | 999 | 0 | 499.313933330031 | 15000.51 | 500.73 | 7733.53519366982 | 40378