|
|
|
|
|
|
Query
--------------------------------------------------------------------
--- Query 06
--- Get the closing price of a set of 10 stocks for a 10-year period
--- and group into weekly, monthly and yearly aggregates. For each
--- aggregate period determine the low, high and average closing price
--- value. Sort the output by id and trade date.
--------------------------------------------------------------------
SELECT Calendar_Year,
Calendar_Year_Month,
Calendar_Week_Number_in_Year,
Stock_name,
MIN(Close_Price),
MAX(Close_Price),
AVG(Close_Price)
FROM StockTransaction_fact A,
Date_Dimension B,
Stock_Dimension C
WHERE A.stock_key = C.stock_key
AND A.date_key = B.date_key
AND Calendar_Year >= 1900
AND Calendar_Year <= 2007
GROUP BY Calendar_Year,
Calendar_Year_Month,
Calendar_Week_Number_in_Year,
Stock_name
ORDER BY Stock_name,
Calendar_Year,
Calendar_Year_Month,
Calendar_Week_Number_in_Year;