42.21. Soda profits

The Coca-Cola Co spreadsheet[1] is missing some calculations.

42.21.1. Loading data

# install the necessary dependencies
import sys
!{sys.executable} -m pip install --quiet ipython-sql

%load_ext sql

# download data file
!curl https://raw.githubusercontent.com/ocademy-ai/machine-learning/main/open-machine-learning-jupyter-book/assets/data/coca_cola_co.sqlite -o ./coca_cola_co.sqlite

# loading from disk
%sql sqlite:///coca_cola_co.sqlite

# clear tmp data file
!rm -rf coca_cola_co.sqlite

Let’s have a look at some sample data.

%%sql

SELECT * FROM "coca-cola-co"

 * sqlite:///coca_cola_co.sqlite
Done.

year	Net operating revenues	Cost of goods sold	Gross Profit
09	30990.00	11088.00	19902.00
10	35119.00	12693.00	22426.00
11	46542.00	18215.00	28327.00
12	48017.00	19053.00	28964.00
13	46854.00	18421.00	28433.00
14	45998.00	17889.00	28109.00
15	44294.00	17482.00
16	41863.00	16465.00
17	35410.00	13255.00
18	31856.00	11770.00

42.21.2. Assignment

Create queries to return the following information:

1. Calculate the Gross profits of FY ‘15, ‘16, ‘17, and ‘18.

   • Gross Profit = Net Operating revenues - Cost of goods sold.

%%sql

SELECT year, "Net Operating Revenues" ____ "Cost of Goods Sold" ______ "Gross Profit"
FROM "coca-cola-co"
WHERE ______ IN ('15', '16', '17', '18');

1. Calculate the average of all the gross profits. Try to do this with a function.

   • Average = Sum of gross profits divided by the number of fiscal years (10).

   • Documentation of the AVERAGE function.

%%sql

SELECT ____("Gross Profit") AS "Average Gross Profit"
______ (
    SELECT ____ - ____ AS "Gross Profit"
    FROM "coca-cola-co"
) AS "All Gross Profits";

42.21.3. Acknowledgments

Thanks to Microsoft for creating the open-source course Data Science for Beginners and the data author Jasmine Greenaway. They inspires the majority of the content in this chapter.

42.21.4. Bibliography

[1]. CocaCola excel. (n.d.). Retrieved 29 May 2023, from https://www.kaggle.com/datasets/yiyiwang0826/cocacola-excel