|
|
|
|
|
|
This query joins the fact table (five million rows) with one dimension table (1,828 rows).
Query
-- The best day of the week in gross profit
-- for each year of operation.
SELECT Calendar_Year,
Day_Of_Week,
SUM(Gross_Profit_Dollar_Amount) AS Profit
FROM Retail_Sales_Fact,
Date_Dimension
WHERE Retail_Sales_Fact.Date_Key = Date_Dimension.Date_Key
GROUP BY Calendar_Year,Day_Of_Week
ORDER BY Calendar_Year,
Profit DESC;
Example
Retail_Single_Node=> \i retail_query_01.sql
calendar_year | day_of_week | profit
---------------+-------------+----------
2000 | Sunday | 24610107
2000 | Tuesday | 24389067
2000 | Thursday | 23973851
2000 | Friday | 23392757
2000 | Saturday | 22134302
2000 | Wednesday | 21427790
2000 | Monday | 20650172
2001 | Thursday | 24057786
2001 | Sunday | 22808366
2001 | Friday | 22262470
2001 | Tuesday | 21207805
2001 | Wednesday | 20648615
2001 | Saturday | 20522518
2001 | Monday | 16566382
2002 | Saturday | 23068736
2002 | Wednesday | 22749773
2002 | Monday | 22728810
2002 | Sunday | 20862246
2002 | Friday | 20825621
2002 | Tuesday | 20034320
2002 | Thursday | 18856255
2003 | Friday | 24563166
2003 | Tuesday | 22913972
2003 | Wednesday | 22255964
2003 | Thursday | 21596220
2003 | Saturday | 21039048
2003 | Monday | 20685036
2003 | Sunday | 20529061
2004 | Friday | 23675620
2004 | Saturday | 22815560
2004 | Wednesday | 21332928
2004 | Tuesday | 21303355
2004 | Sunday | 21190484
2004 | Monday | 20863037
2004 | Thursday | 20419213
(35 rows)