|
|
|
|
|
|
This query joins five million rows of fact table data with three dimension tables (1,828 rows, 250 rows, and 1,000 rows).
Query
-- Promotion Profits by Year, Month, and Region
SELECT Calendar_Year,
Calendar_Month_Name,
Store_Region,
Promotion_Name,
SUM(Gross_Profit_Dollar_Amount) AS Profit
FROM Retail_Sales_Fact POS_Fact,
Date_Dimension Date_Dim,
Store_Dimension Store_Dim,
Promotion_Dimension Prom_Dim
WHERE POS_Fact.Date_Key = Date_Dim.Date_Key
AND POS_Fact.Store_Key = Store_Dim.Store_Key
AND POS_Fact.Promotion_Key = Prom_Dim.Promotion_Key
GROUP BY Calendar_Year,
Calendar_Month_Name,
Promotion_Name,
Store_Region
HAVING SUM(Gross_Profit_Dollar_Amount) >= 4500
ORDER BY Profit DESC;
Example
Retail_Single_Node=> \a
Output format is unaligned.
Retail_Single_Node=> \i retail_query_02.sql
calendar_year|calendar_month_name|store_region|promotion_name|profit
2000|January|West|Summer Cool Sale|97451
2000|October|West|July 4th Discount Sale|96588
2003|March|West|Thanksgiving Super Sellathon|96169
2000|January|West|Thanksgiving Super Sellathon|95184
2000|October|West|Thanksgiving Super Sellathon|95134
2000|January|West|July 4th Super Sale|94871
2000|December|West|Summer Liquidation Promotion|94343
2000|January|West|Summer Liquidation Promotion|94014
2000|January|West|July 4th Cool Sellathon|92744
2004|January|West|Summer Cool Sale|92659
2004|January|West|Thanksgiving Super Sellathon|92310
2000|October|West|Summer Liquidation Promotion|91872
2001|August|West|Thanksgiving Super Sellathon|91837
2001|May|West|Thanksgiving Super Sellathon|91389
2004|January|West|Summer Liquidation Promotion|90615
2000|December|West|Thanksgiving Super Sellathon|90423
2004|January|West|July 4th Discount Sellathon|90282
2003|December|West|Thanksgiving Super Sellathon|89181
2004|December|West|Thanksgiving Super Sellathon|88236
2000|January|West|Winter Cool Sale|88196
2000|December|West|Summer Cool Sale|88191
2002|October|West|July 4th Discount Sale|88081
2000|October|East|Summer Liquidation Promotion|87857
2002|October|West|Summer Liquidation Promotion|87836
2004|January|West|Winter Cool Sale|87751
2001|January|West|July 4th Discount Sale|87743
2003|March|West|July 4th Discount Sale|87475
2002|October|West|Thanksgiving Super Sellathon|87349
2002|June|West|Thanksgiving Super Sellathon|86971
2000|January|West|July 4th Discount Sale|86742
2000|January|West|Christmas Mega Promotion|86585
2003|March|West|Thanksgiving Discount Sellathon|86559
2003|March|West|Winter Cool Sale|86359
2000|December|West|July 4th Cool Sellathon|86148
2002|August|West|Thanksgiving Super Sellathon|85995
2004|January|West|July 4th Cool Sellathon|85840
2001|August|West|Summer Cool Sale|85612
2004|May|West|Thanksgiving Super Sellathon|85199
2003|December|West|July 4th Discount Sale|85069
2000|January|West|Thanksgiving Discount Sellathon|84933
2001|March|West|Thanksgiving Super Sellathon|84698
2001|January|West|Thanksgiving Super Sellathon|84629
2004|January|West|July 4th Discount Sale|84598
2002|October|West|July 4th Super Sale|84324
2000|October|West|July 4th Cool Sellathon|84106
2000|December|West|July 4th Discount Sale|83998
2000|January|East|Thanksgiving Super Sellathon|83824
2003|May|West|Thanksgiving Super Sellathon|83669
2003|May|West|Summer Cool Sale|83669
2004|June|West|Thanksgiving Super Sellathon|83584
2001|May|West|July 4th Discount Sale|83183
2002|October|West|Winter Cool Sale|83147
2000|July|West|Summer Cool Sale|82945
2002|May|West|Summer Liquidation Promotion|82845
2004|January|West|Thanksgiving Liquidation Promotion|82775
2000|January|West|Winter Mega Sale|82716
2004|January|West|Summer Mega Sellathon|82450
2000|December|West|July 4th Super Sale|82383
2000|December|West|Christmas Mega Promotion|82213
2003|May|West|Summer Liquidation Promotion|82160
2004|August|West|Thanksgiving Super Sellathon|82120
2002|October|West|Summer Cool Sale|82047
2000|January|West|Christmas Cool Promotion|81943
2004|January|East|July 4th Super Sale|81908
2004|January|West|July 4th Super Sale|81845
2003|March|West|July 4th Super Sale|81772
2004|August|West|Summer Liquidation Promotion|81650
2000|July|West|Winter Cool Sale|81510
2000|October|West|Winter Cool Sale|81444
2003|March|West|July 4th Cool Sellathon|81417
2001|August|West|July 4th Cool Sellathon|81341
2004|November|West|Thanksgiving Super Sellathon|81327
2000|January|East|Summer Cool Sale|81240
2001|March|West|Summer Liquidation Promotion|81190
2000|October|West|Thanksgiving Discount Sellathon|81172
2001|May|West|July 4th Cool Sellathon|81168
2004|June|West|July 4th Cool Sellathon|81037
2003|February|West|July 4th Discount Sale|80991
2003|December|West|July 4th Super Sale|80985
2003|February|West|Thanksgiving Super Sellathon|80969
2002|October|West|July 4th Cool Sellathon|80917
2000|January|West|Summer Discount Promotion|80890
2002|August|West|Summer Liquidation Promotion|80862
2003|June|West|Winter Cool Sale|80813
2002|December|West|Thanksgiving Super Sellathon|80761
2003|October|West|July 4th Discount Sale|80761
2003|December|West|Summer Liquidation Promotion|80724
2003|December|West|July 4th Discount Sellathon|80719
2003|August|West|July 4th Discount Sale|80700
2000|January|East|July 4th Cool Sellathon|80698
2000|December|East|Thanksgiving Super Sellathon|80656
2004|October|West|Winter Cool Sale|80616
2000|October|West|July 4th Super Sale|80555
2000|November|West|Thanksgiving Super Sellathon|80445
2003|May|West|July 4th Cool Sellathon|80401
2000|December|West|Winter Cool Sale|80394
2003|October|West|July 4th Super Sale|80346
2004|January|East|Summer Liquidation Promotion|80244
2001|January|West|July 4th Cool Sellathon|80191
2004|January|West|Christmas Cool Promotion|80134
2004|January|East|Thanksgiving Super Sellathon|80094
(101 rows)
Retail_Single_Node=>