|
|
|
|
|
|
This query joins five million rows of fact table data with four dimension tables.
Query
-- Most Profitable Seafood Products in the East in 2003
SELECT Product_Description,
SUM(Gross_Profit_Dollar_Amount) AS Profit
FROM Retail_Sales_Fact,
Product_Dimension,
Store_Dimension,
Date_Dimension
WHERE Retail_Sales_Fact.Product_Key = Product_Dimension.Product_Key
AND Retail_Sales_Fact.Store_Key = Store_Dimension.Store_Key
AND Retail_Sales_Fact.Date_Key = Date_Dimension.Date_Key
AND Department_Description = 'Seafood'
AND Store_Region = 'East'
AND Calendar_Year = 2003
GROUP BY Store_Region,
Product_Description
ORDER BY Store_Region,
Profit DESC;
Example
Retail_Single_Node=> \i retail_query_03.sql
------------------------------------------------------
product_description | profit
-----------------------+--------
Seafood Product 10370 | 2432
Seafood Product 47983 | 2331
Seafood Product 43929 | 2095
Seafood Product 6474 | 2008
Seafood Product 18213 | 1976
Seafood Product 53224 | 1935
Seafood Product 57425 | 1896
Seafood Product 10608 | 1888
Seafood Product 2989 | 1869
Seafood Product 258 | 1812
Seafood Product 25835 | 1809
Seafood Product 40207 | 1794
Seafood Product 16271 | 1794
Seafood Product 1429 | 1791
Seafood Product 58142 | 1777
Seafood Product 33695 | 1772
Seafood Product 20455 | 1765
Seafood Product 12616 | 1757
Seafood Product 57498 | 1750
Seafood Product 29837 | 1748
Seafood Product 53700 | 1745
Seafood Product 31991 | 1733
Seafood Product 16584 | 1731
Seafood Product 19347 | 1724
Seafood Product 25424 | 1719
Seafood Product 49094 | 1694
Seafood Product 57111 | 1683
Seafood Product 53686 | 1681
Seafood Product 32016 | 1680
Seafood Product 48506 | 1676
Seafood Product 12294 | 1669
Seafood Product 21983 | 1667
Seafood Product 30662 | 1666
Seafood Product 30073 | 1663
Seafood Product 27621 | 1662
Seafood Product 37650 | 1650
Seafood Product 37755 | 1645
Seafood Product 32757 | 1644
Seafood Product 21454 | 1636
Seafood Product 50994 | 1632
Seafood Product 32028 | 1630
Seafood Product 41263 | 1626
Seafood Product 6438 | 1606
Seafood Product 57315 | 1605
Seafood Product 11539 | 1605
Seafood Product 51685 | 1603
Seafood Product 34664 | 1600
Seafood Product 5798 | 1591