|
|
|
|
|
|
Each example database includes several files containing SQL queries that are intended to represent queries that might be used in a production database. If you copy the query files to a client system, you can connect to the example database and execute the queries using any of the methods described in the SQL Programmer's Guide.
To run an example query using 
vsql on a cluster host:
vsql is the Vertica implementation of psql, a character-based, interactive, front-end that is part of PostgreSQL and used by other database management systems. It allows you to type in SQL statements and see the results. It also provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.
Welcome to the vsql, Vertica_Database v2.1.GA interactive terminal.
Type: \h for help with SQL commands
\? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
Stock_Schema=>
\i meta-command to execute the query script. For example:Reads input from the file filename and executes it as though it had been typed on the keyboard.
Note: To see the lines on the screen as they are read, set the variable ECHO to all.
Stock_Schema=> \i stock_query_01.sql
Clickstream Example Database
Query
-- Customer hitting the web server the most
-- number of times in a day
SELECT Date_Val,
Customer_Name,
COUNT(*) AS Hits
FROM ClickStream_Fact A,
Customer_Dimension B,
Date_Dimension C
WHERE A.Customer_Key = B.Customer_Key
AND A.Date_Key = C.Date_Key
GROUP BY Date_Val,Customer_Name
ORDER BY Hits DESC;
Example
Date_Val | Customer_Name | Hits
------------+---------------+------
2000-11-19 | Michael | 321
2000-03-03 | Michael | 320
2000-12-20 | Sophie | 317
2000-12-03 | Sophie | 314
2000-07-02 | Sophie | 313
2000-05-17 | Michael | 311
Query
-- Client IP hitting the server the most
-- number of times in a day
SELECT Date_Val,
IPAddress_Val,
City,
COUNT(*) AS Hits
FROM ClickStream_Fact A,
IPAddress_Dimension B,
Date_Dimension C
WHERE A.ClientIP_Key = B.IPAddress_Key
AND A.Date_Key = C.Date_Key
GROUP BY Date_Val,IPAddress_Val,City
ORDER BY Hits DESC;
Example
Date_Val | IPaddress_Val | City | Hits
------------+---------------+------------+------
2000-08-06 | 172.16.2.15 | Noida | 11
2000-10-19 | 172.16.1.3 | Tokyo | 10
2000-06-05 | 172.16.2.4 | Paris | 10
2000-07-05 | 172.16.1.6 | London | 10
2000-07-29 | 172.16.1.6 | London | 10
2000-01-19 | 172.16.2.15 | Noida | 10
2000-02-10 | 172.16.0.4 | Detroit | 10
Query
-- Page with the maximum number of hits
-- and total browsing time
SELECT Date_Val,
Page_Name,
SUM(Browsing_Time) AS Browsing_Time,
COUNT(*) AS Hits
FROM ClickStream_Fact A,
Page_Dimension B,
Date_Dimension C
WHERE A.Date_Key = C.Date_Key
AND A.Page_Key = B.Page_Key
GROUP BY Date_Val,Page_Name
ORDER BY Browsing_Time DESC,
Hits DESC;
Example
Date_Val | Page_Name | Browsing_Time | Hits
------------+------------------------------------------------+---------------+-----
2000-06-06 | http://www.Geocities.Yahoo.com/page72.html | 90 | 16
2000-11-19 | http://www.Jewellery.Rediff.com/page23.html | 87 | 11
2000-03-16 | http://www.MP3-Players.Rediff.com/page34.html | 81 | 14
2000-05-04 | http://www.Cricket.Rediff.com/page90.html | 80 | 13
2000-04-27 | http://www.Laptops.Rediff.com/page69.html | 79 | 11
2000-01-20 | http://www.Mobiles.Rediff.com/page97.html | 75 | 12
Query
-- Customers creating more than 5 sessions per day
SELECT Date_Val,
Customer_Name,
SUM(Duration),
COUNT(*) AS Count_Session
FROM ClickStream_Fact A,
Date_Dimension B,
Session_Dimension C,
Customer_Dimension D
WHERE A.Date_Key = B.Date_Key
AND A.Customer_Key = D.Customer_Key
AND A.Session_Key = C.Session_Key
GROUP BY Date_Val,Customer_Name,Duration
HAVING COUNT(*) > 5
ORDER BY Duration DESC;
Example
Date_Val | Customer_Name | SUM | Count_Session
------------+---------------+------+---------------
2000-06-29 | Matthew | 1320 | 11
2000-07-08 | Hannah | 1200 | 10
2000-07-11 | Hannah | 960 | 8
2000-07-12 | Hannah | 840 | 7
2000-07-13 | Hannah | 1800 | 15
2000-07-15 | Hannah | 1920 | 16
Query
-- Customers coming from more than one IP address
SELECT Date_Val,
Customer_Name,
COUNT(ClientIP_Key) AS Client_IPS
FROM ClickStream_Fact A,
Date_Dimension B,
Customer_Dimension C
WHERE A.Date_Key = B.Date_Key
AND A.Customer_Key = C.Customer_Key
AND A.Date_Key > 100
AND A.Date_Key < 105
GROUP BY Date_Val,Customer_Name
HAVING COUNT(ClientIP_Key) > 10
ORDER BY Client_IPs DESC;
Example
Date_Val | Customer_Name | Client_IPS
------------+---------------+------------
2000-04-11 | Sophie | 308
2000-04-11 | Michael | 307
2000-04-11 | Samuel | 224
2000-04-11 | Hannah | 222
2000-04-11 | Emily | 214
2000-04-13 | Sophie | 213
Credit History Example Database
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
Query
-- Overdue statistics for 2001 by Institution
-- a. Avg Overdue (Amount and Days)
-- b. Max Overdue (Amount and Days)
-- c. Min Overdue (Amount and Days)
SELECT Institution_Name,
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,
Institution_Dimension B,
Date_Dimension C
WHERE A.Date_Key = C.Date_Key
AND A.Institution_Key = B.Institution_Key
AND C.Calendar_Year = 2000
GROUP BY Institution_Name
ORDER BY Avg_Amount DESC;
Example
Institution_Name | Max_Days | Min_Days | Avg_Days | Max_Amount | Min_Amount | Avg_Amount | Overdue_Recs
------------------+----------+----------+------------------+------------+------------+------------------+--------------
INSTT#98 | 997 | 0 | 506.386450381679 | 14986.93 | 511.55 | 8034.51529580153 | 1048
INSTT#57 | 999 | 2 | 494.70480081716 | 15000.01 | 508.57 | 8023.94215526047 | 979
INSTT#83 | 999 | 0 | 508.528806584362 | 14994.48 | 502.39 | 8019.49127572016 | 972
INSTT#56 | 999 | 3 | 516.19877675841 | 14979.93 | 511.46 | 7998.86175331295 | 981
INSTT#45 | 997 | 1 | 498.116596638655 | 14994.69 | 507.47 | 7985.12201680672 | 952
INSTT#66 | 998 | 0 | 488.579420579421 | 14990.66 | 501.3 | 7973.51433566434 | 1001
INSTT#84 | 998 | 0 | 505.276302851524 | 14985.32 | 504.76 | 7964.23406096362 | 1017
INSTT#90 | 996 | 1 | 510.30303030303 | 14990.34 | 536.82 | 7951.99204301075 | 1023
INSTT#44 | 998 | 0 | 484.883883883884 | 14970.27 | 525.28 | 7945.75424424424 | 999
INSTT#69 | 999 | 2 | 507.625502008032 | 14986.1 | 509.98 | 7936.75596385542 | 996
INSTT#93 | 998 | 0 | 502.520669291339 | 15000.51 | 502.02 | 7936.17729330709 | 1016
INSTT#73 | 998 | 0 | 491.066198595787 | 14993.14 | 559.45 | 7924.45994984955 | 997
Query
-- Overdue mortgage statistics by year with mortgage type
SELECT Mortgage_Type,
AVG(Days_Overdue) AS Avg_Days,
AVG(Outstanding_Amount) AS Avg_Amount,
COUNT(*) AS Overdue_Recs
FROM CreditHistory_Fact A,
Mortgage_Dimension B,
Date_Dimension C
WHERE A.Mortgage_Key = B.Mortgage_Key
AND A.Date_Key = C.Date_Key
GROUP BY Calendar_Year,Mortgage_Type
ORDER BY Calendar_Year,
Mortgage_Type;
Example
Mortgage_Type | Avg_Days | Avg_Amount | Overdue_Recs
---------------+------------------+------------------+--------------
Car | 499.179450730653 | 7758.45090843616 | 105522
Home | 499.670780499164 | 7742.27507610237 | 94478
(2 rows)
Query
-- Overdue mortgage statistics by year with tenure
SELECT Mortgage_Type,
Mortgage_Tenure,
AVG(Days_Overdue) AS Avg_Days,
AVG(Outstanding_Amount) AS Avg_Amount,
COUNT(*) AS Record_Count
FROM CreditHistory_Fact A,
Mortgage_Dimension B,
Date_Dimension C
WHERE A.Mortgage_Key = B.Mortgage_Key
AND A.Date_Key = C.Date_Key
GROUP BY Calendar_Year,Mortgage_Type,Mortgage_Tenure
ORDER BY Calendar_Year,
Mortgage_Type,
Mortgage_Tenure;
Example
Mortgage_Type | Mortgage_Tenure | Avg_Days | Avg_Amount | Record_Count
--------------+-----------------+------------------+------------------+------------
Car | 12 | 498.664561695056 | 7745.60994349813 | 24070
Car | 24 | 502.332021237642 | 7753.32524533138 | 21848
Car | 36 | 500.580798992262 | 7793.29573420911 | 22228
Car | 48 | 498.262124831239 | 7730.3517000727 | 19258
Car | 60 | 495.317695109836 | 7768.80918644442 | 18118
Home | 60 | 500.719860896445 | 7858.66575637558 | 20704
Home | 96 | 500.386262760763 | 7710.50094429649 | 18024
Home | 120 | 496.92023054755 | 7751.82940172911 | 17350
Home | 180 | 498.150733659404 | 7721.11076144953 | 17992
Home | 240 | 501.653077224618 | 7662.79473049784 | 20408
(10 rows)
Query
-- Overdue mortgage statistics by year with account type
SELECT Account_Type,
AVG(Days_Overdue) AS Avg_Days,
AVG(Outstanding_Amount) AS Avg_Amount,
COUNT(*) AS Record_Count
FROM CreditHistory_Fact A,
AccountType_Dimension B,
Date_Dimension C
WHERE A.AccountType_Key = B.AccountType_Key
AND A.Date_Key = C.Date_Key
GROUP BY Calendar_Year,Account_Type
ORDER BY Calendar_Year,
Account_Type;
Example
Account_Type | Avg_Days | Avg_Amount | Record_Count
--------------+------------------+------------------+--------------
Checking | 500.261721483555 | 7741.41345971209 | 40012
Current | 501.090460467923 | 7785.66681471225 | 28167
Saving | 496.856415574621 | 7731.76984318532 | 31821
Stock Exchange Example Database
Query
-----------------------------------------------------------------------
--- QUERY #1
--- Stocks that gained between 70% and 75% on a given day
----------------------------------------------------------------------
SELECT B.Stock_Name,
MIN(A.Close_Price),
MAX(A.Close_Price)
FROM StockTransaction_Fact A,
Stock_Dimension B
WHERE A.Date_Key > 50
AND A.Date_Key < 53
AND A.Stock_Key = B.Stock_Key
AND (((A.close_Price - A.Previous_Close) * 100) / A.Previous_Close) > 70
AND (((A.close_Price - A.Previous_Close) * 100) / A.Previous_Close) < 75
GROUP BY B.Stock_Name
ORDER BY B.Stock_Name;
Query
-----------------------------------------------------------------------
--- QUERY #2
--- Stocks with maximum traded quantity and value in a
--- given settlement period
-----------------------------------------------------------------------
SELECT Settlement_Description,
Stock_Name,
SUM(Total_Traded_Quantity) AS Total_Traded_Qty,
SUM(Total_Turnover) AS Total_Trade_value
FROM StockTransaction_Fact A,
Settlement_Dimension B,
Stock_Dimension C,
Date_Dimension D
WHERE A.Settlement_Key = B.Settlement_Key
AND A.Stock_Key = C.Stock_Key
AND A.Date_Key = D.Date_Key
AND B.Settlement_Description = '2000010'
AND D.Calendar_Month_Number_in_Year = 1
AND D.Calendar_Year = 2004
GROUP BY Settlement_Description,
Stock_Name
ORDER BY Settlement_Description,
Stock_Name;
Query
-----------------------------------------------------------------------
--- QUERY #3
--- Stocks with maximum traded quantity and value in a
--- given week of the year
-----------------------------------------------------------------------
SELECT Day_Number_in_Calendar_Month,
C.Stock_Name,
SUM(Total_Traded_Quantity) AS Total_Traded_Qty,
SUM(Total_Turnover) AS Total_Trade_value
FROM StockTransaction_Fact A,
Date_Dimension B,
Stock_Dimension C
WHERE A.Date_Key = B.Date_Key
AND A.Stock_Key = C.Stock_Key
AND B.Calendar_Week_Number_in_Year = 7
GROUP BY Day_Number_in_Calendar_Month,
Stock_Name;
Query
--------------------------------------------------------------------
--- Query 04
--- Types of traders who have a maximum turnover in a given week
--------------------------------------------------------------------
SELECT TraderType,
SUM(Total_Traded_Quantity) AS Total_Traded_Quantity,
SUM(Total_Deliverable_Qty) AS Total_Deliverable_Qty,
SUM(Total_Deliverable_Qty)
/ SUM(Total_Traded_Quantity) AS Delivery_Trade_Ratio
FROM StockTransaction_Fact A,
Date_Dimension B,
Trader_Dimension C
WHERE A.Date_Key = B.Date_Key
AND A.Trader_Key = C.Trader_Key
AND B.Calendar_Week_Number_in_Year = 9
GROUP BY TraderType
ORDER BY Delivery_Trade_Ratio;
Query
--------------------------------------------------------------------
--- Query 05
--- Exchange that has a maximum turnover in a year
--------------------------------------------------------------------
SELECT Calendar_Year,
Exchange_Name,
SUM(Total_Traded_Quantity) AS Total_Traded_Quantity,
SUM(Total_Turnover) AS Total_Trade_value
FROM StockTransaction_Fact A,
Date_Dimension B,
Exchange_Dimension C
WHERE A.Date_Key = B.Date_Key
AND A.Exchange_Key = C.Exchange_Key
GROUP BY Calendar_Year,
Exchange_Name
ORDER BY Total_Trade_value DESC,
Total_Traded_Quantity;
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;
Query
---------------------------------------------------------------------
----- Query # 07
-- For each stock in a specified list of 1000 stocks, find the
-- differences between the daily high and daily low on the day of
-- each split event during a specified period.
---------------------------------------------------------------------
SELECT A.High_Price - A.Low_Price,
A.Close_Price,
C.Split_Date
FROM StockTransaction_Fact A,
Stock_Dimension B,
Split_Dimension C,
Date_Dimension D
WHERE A.Stock_Key = B.Stock_Key
AND B.split_Key = C.Split_Key
AND A.Date_Key = D.Date_Key
AND D.Calendar_Year = 2002
AND D.Calendar_Week_Number_in_Year = 10
AND D.Day_of_Week = 'Monday'
ORDER BY A.Stock_Key;
Retail Sales Example Database
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)
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=>
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
Telecom Example Database
Query
-- Best month of the year in terms of
-- minutes of usage for each year of operation.
SELECT Calendar_Year,
Calendar_Year_Month,
SUM(Total_Minutes) AS Total_Minutes
FROM Billing_Fact,
Date_Dimension
WHERE Billing_Fact.Date_Key = Date_Dimension.Date_Key
GROUP BY Calendar_Year,Calendar_Year_Month
ORDER BY Calendar_Year,
Calendar_Year_Month;
Example
Calendar_Year | Calendar_Year_Month | Total_Minutes
--------------+---------------------+---------------
2000 | 1 | 1451
2000 | 2 | 1616
2000 | 3 | 1397
2000 | 4 | 1334
2000 | 5 | 1076
(17 rows)
Query
-- Best rate plan in use
SELECT Calendar_Year,
Calendar_Year_Month,
Plan_Name,
SUM(Number_Of_Calls) AS Calls,
SUM(Total_Minutes) AS Total_Minutes
FROM Billing_Fact Bill_Fact,
Date_Dimension Date_Dim,
Rate_Plan_Dimension Rate_Dim
WHERE Bill_Fact.Date_Key = Date_Dim.Date_Key
AND Bill_Fact.Plan_Key = Rate_Dim.Plan_Key
GROUP BY Calendar_Year,Calendar_Year_Month,Plan_Name
HAVING SUM(Number_Of_Calls) >= 10
ORDER BY Calls;
Example
Calendar_Year | Calendar_Year_Month | Plan_Name | Calls | Total_Minutes
---------------+---------------------+-------------------+-------+---------------
2000 | 12 | Freedom_40 | 10 | 18
2000 | 9 | Youth_45 | 10 | 48
2000 | 2 | Freedom_30 | 10 | 49
2000 | 6 | Flexi_40 | 10 | 35
2000 | 1 | Flexi_30 | 10 | 36
2000 | 9 | Youth_30 | 10 | 81
2000 | 6 | Youth_25 | 10 | 55
2000 | 10 | Executive_40 | 10 | 42
(319 rows)
Query
-- Customer using the most roaming minutes in 2000
SELECT Cust_Name,
Calendar_Year,
SUM(Total_Roaming_minutes) AS TOTAL_ROAMING
FROM Billing_Fact Bill_Fact,
Date_Dimension Date_Dim,
Customer_Details_Dimension Cust_Dim
WHERE Bill_Fact.Cust_Key = Cust_Dim.Cust_Key
AND Bill_Fact.Date_Key = Date_Dim.Date_Key
AND Date_Dim.Calendar_Year = 2000
AND Bill_Fact.Roaming_Flag = 1
GROUP BY Cust_Name,Calendar_Year
ORDER BY Cust_Name,
TOTAL_ROAMING DESC;
Example
Cust_Name | Calendar_Year | Total_Roaming
-------------+---------------+---------------
(null) | 2000 | 361
Abigail | 2000 | 323
Andrew | 2000 | 216
Anthony | 2000 | 384
AshleyJack | 2000 | 378
Ava | 2000 | 243
(29 rows)
Query
-- Total service tax and surcharge paid to government in 2000
SELECT Calendar_Year,
Calendar_Year_Month,
SUM(Service_Tax) AS SERVICE_TAX,
SUM(SURCHARGE) AS SURCHARGE
FROM Billing_Fact Bill_Fact,
Date_Dimension Date_Dim
WHERE Bill_Fact.Date_Key = Date_Dim.Date_Key
AND Date_Dim.Calendar_Year = 2000
GROUP BY Calendar_Year,Calendar_Year_Month
ORDER BY Calendar_Year,
Calendar_Year_Month DESC;
Example
Calendar_Year | Calendar_Year_Month | Service_Tax | Surcharge
---------------+---------------------+-------------+-----------
2000 | 12 | 67.405 | 6.7405
2000 | 11 | 45.615 | 4.5615
2000 | 10 | 49.315 | 4.9315
2000 | 9 | 63.495 | 6.3495
2000 | 8 | 62.53 | 6.253
(12 rows)
Query
-- Total number of calls with abnormal termination code
SELECT Calendar_Year,
Termination_Description,
SUM(Number_Of_Calls) AS CALL_COUNT
FROM Billing_Fact Bill_Fact,
Date_Dimension Date_Dim,
Call_Termination_Dimension Term_Dim
WHERE Bill_Fact.Date_Key = Date_Dim.Date_Key
AND Bill_Fact.Termination_Key = Term_Dim.Termination_Key
AND Term_Dim.Termination_Type = 'Abnormal'
GROUP BY Calendar_Year,Termination_Description
ORDER BY Calendar_Year,
CALL_COUNT;
Example
Calendar_Year | Termination_Description | Call_Count
---------------+---------------------------+------------
2000 | Abnormal Call Termination | 2010
2001 | Abnormal Call Termination | 873
(2 rows)