Chapter 4. Statistics, analytic, OLAP functions in business scenarios 167
4.2.6 Determine the most profitable items and where they are sold
Being able to determine one’s most profitable items and where they are sold
enables an organization to optimize product purchases and product distribution
for maximum profitability.
To establish the profit on an item you need to know its cost to you and the price at
which it is sold. In this example we will use the sales figures from a fictional
world-wide Coffee Retailer and cross calculate the profit based on the profit of
these items in their respective countries.
Data
Our data is mainly taken from pricing and transactions.
? The PRICING view includes store, item, and sales information.
? The TRANSACTIONS view includes store, item, cost, and price sold.
BI functions showcased
RANK, OVER, PARTITION BY, ORDER BY
Steps
This query is answered via multiple steps as follows:
1. For each variety of coffee, determine the store with the highest profit.
2. For each store, determine the coffee variety with the highest profit.
3. Determine the most profitable product in each store.
4. Determine the most profitable store for each variety of coffee.
Step 1
The query shown in Example 4-12 calculates the store with the highest profit on
the different varieties of coffee:
Example 4-12 Store with the highest profit on the different varieties of coffee
SELECT store, item, profit
FROM
(
SELECT store, item, price - cost AS profit,
RANK() OVER (PARTITION BY item ORDER BY price - cost DESC) AS
rank_profit
FROM pricing
) AS ranked_profit
WHERE rank_profit = 1
ORDER BY profit DESC
168 High-Function Business Intelligence in e-business
Figure 4-13 shows the results of this query.
Figure 4-13 Store with highest profit of each variety of coffee
Step 2
The query shown in Example 4-13 determines the coffee variety delivering the
highest profit in each store:
Example 4-13 Coffee variety delivering the highest profit in each store
SELECT store, item, profit
FROM
(
SELECT item, store, price - cost AS profit,
RANK() OVER (PARTITION BY store ORDER BY price - cost DESC) AS
rank_profit
FROM pricing
) AS ranked_profit
WHERE rank_profit = 1
ORDER BY profit DESC
Figure 4-14 shows the results of this query.
Figure 4-14 Highest profit of all varieties of coffee in a given store
In the foregoing examples it is obvious that New York has the highest profits for
most of the varieties. However, this does not necessarily mean the most profits.
Chapter 4. Statistics, analytic, OLAP functions in business scenarios 169
Step 3
The query shown in Example 4-14 calculates the most profitable product in each
store.
Example 4-14 Most profitable product in each store
WITH tt AS
(
SELECT store, item, SUM(sales) AS total
FROM transactions
GROUP BY store, item
)
SELECT store, item, total_profit
FROM
(
SELECT a.store,a.item,b.total*(a.price - a.cost) AS total_profit,
RANK() OVER (PARTITION BY b.store ORDER BY b.total(a.price - a.cost)
DESC) AS rank_profit
FROM pricing a, tt b
WHERE a.store=b.store AND a.item=b.item
) AS ranked_profit
WHERE rank_profit = 1
ORDER BY 3 DESC
Figure 4-15 shows the results of this query.
Figure 4-15 Most profitable product in each store
Step 4
The query shown in Example 4-15 calculates the most profitable store for each
variety of coffee:
Example 4-15 Most profitable store for each variety of coffee
WITH tt AS
(
SELECT item, store, SUM(sales) AS total
FROM transactions
GROUP BY store, item
)
SELECT item, store, total_profit
FROM
(
170 High-Function Business Intelligence in e-business
SELECT a.store,a.item,b.total*(a.price - a.cost) AS total_profit,
RANK() OVER (PARTITION BY b.item ORDER BY b.total(a.price - a.cost)
DESC) AS rank_profit
FROM pricing a, tt b
WHERE a.store=b.store AND a.item=b.item
) AS ranked_profit
WHERE rank_profit = 1
ORDER BY 3 DESC
Figure 4-16 shows the results of this query.
Figure 4-16 Most profitable store for each variety of coffee
The data from these results is charted in Figure 4-17 and Figure 4-18.
Figure 4-17 Total profit by store
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.