Skip to content

Using SQL For Profit Analysis of International Breweries

From the international breweries data recorded for a duration of three years, you are directed to do the following analyses to aid better decision making in order to maximize profit and reduce loss to the lowest minimum.

Spinner
DataFrameas
df
variable
SELECT *
FROM 'International_Breweries.csv'

Data Exploration

A good practice I learned before performing any tasks or answering business questions is to understand that data that we have. With some basic queries, we find out the following information.

  • There are 1047 records here of distinct beer sales
  • Each of these sales was done by 11 sales reps
  • There are 7 distinct beer brands
  • The sales are across 5 African countries Now we have some information to work with, below are the queries used to gather this information. Time to answer some business questions.
Spinner
DataFrameas
df1
variable
SELECT DISTINCT BRANDS 
FROM 'International_Breweries.csv'
Spinner
DataFrameas
df2
variable
SELECT DISTINCT SALES_REP
FROM 'International_Breweries.csv'
Spinner
DataFrameas
df
variable
SELECT DISTINCT YEARS
FROM 'International_Breweries.csv'

Profit Analysis

  1. Within the space of the last three years, what was the profit worth of the breweries, inclusive of the anglophone and the francophone territories?
  2. Compare the total profit between these two territories in order for the territory manager, Mr. Stone made a strategic decision that will aid profit maximization in 2020.
  3. Country that generated the highest profit in 2019
  4. Help him find the year with the highest profit.
  5. Which month in the three years was the least profit generated?
  6. Who was the sales rep with the minimum profit in the month of December 2018?
  7. Compare the profit in percentage for each of the month in 2019
  8. Which particular brand generated the highest profit in Senegal?
Spinner
DataFrameas
df
variable
-- Within the space of the last three years, what was the profit worth of the breweries, inclusive of the anglophone and the francophone territories?
SELECT OPHONE,SUM(PROFIT) AS TOTAL_PROFIT
FROM(
SELECT *,
       CASE WHEN COUNTRIES IN ('Ghana', 'Nigeria') THEN 'Anglophone'
	   ELSE 'Francophone' END AS OPHONE
FROM 'International_Breweries.csv') AS s
GROUP BY OPHONE
Spinner
DataFrameas
df5
variable
-- Country that generated the highest profit in 2019
SELECT COUNTRIES, SUM(PROFIT) AS PROFIT
FROM 'International_Breweries.csv'
WHERE YEARS = 2019
GROUP BY COUNTRIES
ORDER BY PROFIT DESC
c_2019 = ib[ib["YEARS"]==2019].groupby("COUNTRIES")["PROFIT"].sum().sort_values(ascending=False)

c_2019.plot.bar(rot=45)
plt.ylabel('PROFITS')
plt.title('COUNTRIES PROFITS IN 2019')

Profits of Countries from 2017-2019

Initially, the task was to find the country with the highest profit in 2019, and the result (above) was Ghana. Taking it a step further, obtaining the sums of profit of each of the countries over all the years can be made possible using Common Table Expressions (CTEs) and the result of this query is below.

Spinner
DataFrameas
df3
variable
Run cancelled

WITH PROFIT_2019 AS (
SELECT COUNTRIES, SUM(PROFIT) AS PROFIT
FROM 'International_Breweries.csv'
WHERE YEARS = 2019
GROUP BY COUNTRIES),
PROFIT_2018 AS (
SELECT COUNTRIES, SUM(PROFIT) AS PROFIT
FROM 'International_Breweries.csv'
WHERE YEARS = 2018
GROUP BY COUNTRIES) ,
PROFIT_2017 AS (
SELECT COUNTRIES, SUM(PROFIT) AS PROFIT
FROM 'International_Breweries.csv'
WHERE YEARS = 2017
GROUP BY COUNTRIES)

SELECT p19.COUNTRIES, 
       p19.PROFIT AS PROFIT_2019, 
	   p18.PROFIT AS PROFIT_2018, 
	   p17.PROFIT AS PROFIT_2017
FROM PROFIT_2019 AS p19
INNER JOIN PROFIT_2018 AS p18
ON p19.COUNTRIES = p18.COUNTRIES
INNER JOIN PROFIT_2017 AS p17
ON p18.COUNTRIES = p17.COUNTRIES

Alternatively, we could just group by both country and profit and obtain a similar table with a different structure.

Spinner
DataFrameas
df4
variable
Run cancelled

SELECT COUNTRIES, YEARS, SUM(PROFIT) AS PROFIT
FROM 'International_Breweries.csv'
GROUP BY COUNTRIES, YEARS
ORDER BY COUNTRIES ASC, YEARS ASC;
Spinner
DataFrameas
df6
variable
Run cancelled
-- Find out the details of beers consumed in the past three years in Nigeria.
SELECT BRANDS, SUM(PROFIT) AS PROFIT
FROM 'International_Breweries.csv'
WHERE COUNTRIES = 'Nigeria'
GROUP BY BRANDS
‌
‌
‌