{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "# Guided Project: Sales Performance Analysis with SQL\n", "## Learn to combine SQL's skills we've learned to perform practical data analysis\n", "## Dataquest" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%load_ext sql\n", "%sql sqlite://\n", "%sql attach \"sales.db\" as sales" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Guided Project Introduction" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idnamedescription
1productsItems in the stores
2regionsGeographic areas where stores exist
3salesTransactions in the store alongside with product, region, and time dimensions
4sales_detailsTransactions in the stores with several details
5table_nameTables in the database
6timeDates
" ], "text/plain": [ "[(1, 'products', 'Items in the stores'),\n", " (2, 'regions', 'Geographic areas where stores exist'),\n", " (3, 'sales', 'Transactions in the store alongside with product, region, and time dimensions'),\n", " (4, 'sales_details', 'Transactions in the stores with several details'),\n", " (5, 'table_name', 'Tables in the database'),\n", " (6, 'time', 'Dates')]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Title: Sales Performance Analysis\n", "-- Description: we are trying to analyze the sales.db databases to find meanningful trends using SQL.\n", "-- Goal: Our goal is to find the share of sales of the top three regions, the frequently bought products in these top three countrie and the most suitable days for intense marketing and promotion?\n", "\n", "-- table_name table's content.\n", "SELECT *\n", " FROM table_name;\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Getting to know the database" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
product_idproduct_nameproduct_description
1Puff Pastry - SlabNone
2Lamb - Loin ChopsNone
3Flour - Fast / RapidNone
" ], "text/plain": [ "[(1, 'Puff Pastry - Slab', None),\n", " (2, 'Lamb - Loin Chops', None),\n", " (3, 'Flour - Fast / Rapid', None)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- the first three rows of each table from our database\n", "\n", "SELECT *\n", " FROM products\n", " LIMIT 3;" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
region_idregion_nameregion_description
1IndonesiaNone
2PortugalNone
3NetherlandsNone
" ], "text/plain": [ "[(1, 'Indonesia', None), (2, 'Portugal', None), (3, 'Netherlands', None)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT *\n", " FROM regions\n", " LIMIT 3;" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sales_idtime_idproduct_idregion_idtransaction_amount
4868227154-39864
543668472479272
6529176478-33632
" ], "text/plain": [ "[(4, 868, 227, 154, -39864),\n", " (5, 436, 684, 724, 79272),\n", " (6, 529, 176, 478, -33632)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT *\n", " FROM sales\n", " LIMIT 3;" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
time_idtime_datetimetime_yeartime_monthtime_daytime_hourtime_minutetime_secondtime_day_of_weekproduct_nameregion_nametransaction_amount
8682013-06-26 13:50:3620136261350364Chickensplit HalfPakistan-39864
4362012-06-14 09:34:242012614934245Soup - Beef Conomme, DryPoland79272
5292015-08-12 02:58:162015812258164Tea - Earl GreyChina-33632
" ], "text/plain": [ "[(868, '2013-06-26 13:50:36', 2013, 6, 26, 13, 50, 36, 4, 'Chickensplit Half', 'Pakistan', -39864),\n", " (436, '2012-06-14 09:34:24', 2012, 6, 14, 9, 34, 24, 5, 'Soup - Beef Conomme, Dry', 'Poland', 79272),\n", " (529, '2015-08-12 02:58:16', 2015, 8, 12, 2, 58, 16, 4, 'Tea - Earl Grey', 'China', -33632)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT *\n", " FROM sales_details\n", " LIMIT 3;" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
time_idtime_datetimetime_yeartime_monthtime_daytime_hourtime_minutetime_secondtime_day_of_week
12017-10-11 19:52:57201710111952574
22018-01-24 19:15:2420181241915244
32017-12-06 19:45:5820171261945584
" ], "text/plain": [ "[(1, '2017-10-11 19:52:57', 2017, 10, 11, 19, 52, 57, 4),\n", " (2, '2018-01-24 19:15:24', 2018, 1, 24, 19, 15, 24, 4),\n", " (3, '2017-12-06 19:45:58', 2017, 12, 6, 19, 45, 58, 4)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT *\n", " FROM time\n", " LIMIT 3;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "# Identifying the Tables We Need" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
selected_table
sales_details
" ], "text/plain": [ "[('sales_details',)]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Selecting the identified table.\n", "\n", "SELECT 'sales_details' AS selected_table;\n" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
num_rows
1000
" ], "text/plain": [ "[(1000,)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- Counting the rows of the selected table.\n", "\n", "SELECT COUNT(*) AS num_rows\n", " FROM sales_details;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "# Filtering interesting rows\n" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
time_idtime_datetimetime_yeartime_monthtime_daytime_hourtime_minutetime_secondtime_day_of_weekproduct_nameregion_nametransaction_amount
4362012-06-14 09:34:242012614934245Soup - Beef Conomme, DryPoland79272
1462015-02-06 12:18:182015261218186Wine - Spumante Bambino WhiteFrance87806
4832012-12-01 23:08:122012121238127Ketchup - TomatoKazakhstan98243
" ], "text/plain": [ "[(436, '2012-06-14 09:34:24', 2012, 6, 14, 9, 34, 24, 5, 'Soup - Beef Conomme, Dry', 'Poland', 79272),\n", " (146, '2015-02-06 12:18:18', 2015, 2, 6, 12, 18, 18, 6, 'Wine - Spumante Bambino White', 'France', 87806),\n", " (483, '2012-12-01 23:08:12', 2012, 12, 1, 23, 8, 12, 7, 'Ketchup - Tomato', 'Kazakhstan', 98243)]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Filtering the rows related to positive values from the `transaction_amount` column.\n", "\n", "SELECT *\n", " FROM sales_details\n", " WHERE transaction_amount >= 0 \n", " LIMIT 3;" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
num_positive_transactionsnum_rows
494494
" ], "text/plain": [ "[(494, 494)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Counting the positive values from the `transaction_amount` column compared to the number of rows in the filtered table.\n", "\n", "SELECT COUNT(transaction_amount) AS num_positive_transactions, COUNT(*) AS num_rows\n", " FROM sales_details\n", " WHERE transaction_amount >= 0;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "3. Since the `num_positive_transactions` and `num_rows` are the same, we can conclude that there is no missing values in the `transaction_amount` column." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "# Total Transaction Amount for Each Region\n" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
region_nametotal_transaction_amount
Afghanistan131159
Albania235171
Argentina340583
Armenia77199
Australia187963
Azerbaijan159200
Bahamas148728
Bangladesh154916
Belarus25315
Benin34352
Bolivia10961
Brazil488294
Burkina Faso38329
Cameroon44091
Canada125266
China3587812
Colombia356547
Croatia140920
Cuba196686
Cyprus577
Czech Republic94021
Denmark66339
Dominican Republic126001
Ecuador26411
Egypt59730
Estonia32547
Ethiopia276368
Finland196962
France871335
Georgia90044
Germany74158
Greece346326
Guatemala9567
Guinea49896
Guyana124443
Honduras169520
Hungary80988
Indonesia2565442
Iran353566
Iraq96055
Ireland89760
Israel81269
Ivory Coast142272
Japan138545
Kazakhstan121368
Kyrgyzstan95798
Laos119385
Latvia74817
Libya81214
Luxembourg70793
Macedonia46793
Madagascar92723
Malawi122046
Mali85633
Mauritius42660
Mexico338072
Mongolia6955
Morocco97271
Netherlands87392
New Caledonia31718
Nicaragua54865
Nigeria150886
Norway214806
Pakistan121607
Palestinian Territory291565
Peru190533
Philippines1097122
Poland1342022
Portugal559054
Russia1053683
Saudi Arabia28475
Serbia95519
Sierra Leone41685
Slovenia47833
Somalia66332
South Korea229805
Spain355871
Sweden777801
Switzerland131534
Syria123207
Tajikistan58541
Tanzania245208
Thailand413431
Togo201465
Turkmenistan47280
Uganda190927
Ukraine228561
United Kingdom74921
United States1005656
Uzbekistan68969
Venezuela112740
Vietnam259546
Yemen22777
" ], "text/plain": [ "[('Afghanistan', 131159),\n", " ('Albania', 235171),\n", " ('Argentina', 340583),\n", " ('Armenia', 77199),\n", " ('Australia', 187963),\n", " ('Azerbaijan', 159200),\n", " ('Bahamas', 148728),\n", " ('Bangladesh', 154916),\n", " ('Belarus', 25315),\n", " ('Benin', 34352),\n", " ('Bolivia', 10961),\n", " ('Brazil', 488294),\n", " ('Burkina Faso', 38329),\n", " ('Cameroon', 44091),\n", " ('Canada', 125266),\n", " ('China', 3587812),\n", " ('Colombia', 356547),\n", " ('Croatia', 140920),\n", " ('Cuba', 196686),\n", " ('Cyprus', 577),\n", " ('Czech Republic', 94021),\n", " ('Denmark', 66339),\n", " ('Dominican Republic', 126001),\n", " ('Ecuador', 26411),\n", " ('Egypt', 59730),\n", " ('Estonia', 32547),\n", " ('Ethiopia', 276368),\n", " ('Finland', 196962),\n", " ('France', 871335),\n", " ('Georgia', 90044),\n", " ('Germany', 74158),\n", " ('Greece', 346326),\n", " ('Guatemala', 9567),\n", " ('Guinea', 49896),\n", " ('Guyana', 124443),\n", " ('Honduras', 169520),\n", " ('Hungary', 80988),\n", " ('Indonesia', 2565442),\n", " ('Iran', 353566),\n", " ('Iraq', 96055),\n", " ('Ireland', 89760),\n", " ('Israel', 81269),\n", " ('Ivory Coast', 142272),\n", " ('Japan', 138545),\n", " ('Kazakhstan', 121368),\n", " ('Kyrgyzstan', 95798),\n", " ('Laos', 119385),\n", " ('Latvia', 74817),\n", " ('Libya', 81214),\n", " ('Luxembourg', 70793),\n", " ('Macedonia', 46793),\n", " ('Madagascar', 92723),\n", " ('Malawi', 122046),\n", " ('Mali', 85633),\n", " ('Mauritius', 42660),\n", " ('Mexico', 338072),\n", " ('Mongolia', 6955),\n", " ('Morocco', 97271),\n", " ('Netherlands', 87392),\n", " ('New Caledonia', 31718),\n", " ('Nicaragua', 54865),\n", " ('Nigeria', 150886),\n", " ('Norway', 214806),\n", " ('Pakistan', 121607),\n", " ('Palestinian Territory', 291565),\n", " ('Peru', 190533),\n", " ('Philippines', 1097122),\n", " ('Poland', 1342022),\n", " ('Portugal', 559054),\n", " ('Russia', 1053683),\n", " ('Saudi Arabia', 28475),\n", " ('Serbia', 95519),\n", " ('Sierra Leone', 41685),\n", " ('Slovenia', 47833),\n", " ('Somalia', 66332),\n", " ('South Korea', 229805),\n", " ('Spain', 355871),\n", " ('Sweden', 777801),\n", " ('Switzerland', 131534),\n", " ('Syria', 123207),\n", " ('Tajikistan', 58541),\n", " ('Tanzania', 245208),\n", " ('Thailand', 413431),\n", " ('Togo', 201465),\n", " ('Turkmenistan', 47280),\n", " ('Uganda', 190927),\n", " ('Ukraine', 228561),\n", " ('United Kingdom', 74921),\n", " ('United States', 1005656),\n", " ('Uzbekistan', 68969),\n", " ('Venezuela', 112740),\n", " ('Vietnam', 259546),\n", " ('Yemen', 22777)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Computing the total positive transaction amount for each region using the `GROUP BY` clause.\n", "\n", "SELECT region_name, SUM(transaction_amount) AS total_transaction_amount\n", " FROM sales_details\n", " WHERE transaction_amount >= 0\n", " GROUP BY region_name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "# Top Three Regions\n" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
region_nametotal_transaction_amount
China3587812
Indonesia2565442
Poland1342022
" ], "text/plain": [ "[('China', 3587812), ('Indonesia', 2565442), ('Poland', 1342022)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "\n", "-- Finding the top three regions in relation to the total positive transaction amount.\n", "\n", "SELECT region_name, SUM(transaction_amount) AS total_transaction_amount\n", " FROM sales_details\n", " WHERE transaction_amount >= 0\n", " GROUP BY region_name\n", " ORDER BY total_transaction_amount DESC\n", " LIMIT 3;\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Share of Sales\n" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
region_nametotal_transaction_amount_props
China14.909148950077872
Indonesia10.660691502449314
Poland5.576770993653348
" ], "text/plain": [ "[('China', 14.909148950077872),\n", " ('Indonesia', 10.660691502449314),\n", " ('Poland', 5.576770993653348)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Computes the share of sales of the top three regions in relation to the total positive transaction amount.\n", "\n", "SELECT region_name, SUM(transaction_amount) * 100.0 / 24064499 AS total_transaction_amount_props\n", " FROM sales_details\n", " WHERE transaction_amount >= 0\n", " GROUP BY region_name\n", " ORDER BY total_transaction_amount_props DESC\n", " LIMIT 3;\n", " \n" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
top_3_share_market
31.15
" ], "text/plain": [ "[(31.15,)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Summing the share of sales\n", "SELECT ROUND(14.909148950077872, 2) + ROUND(10.660691502449314, 2) + ROUND(5.576770993653348, 2) AS top_3_share_market" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "3. The highest sales in our store come from Asian countries. Our customers are probably many in this part of the world." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "# Finding Frequently Bought Products\n" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
region_nameproduct_namenum_transactionsavg_transaction_amount
IndonesiaBeef - Rib Eye Aaa360284.333333333336
ChinaCoke - Diet, 355 Ml246720.0
IndonesiaCheese - La Sauvagine254260.5
IndonesiaFennel - Seeds236930.0
IndonesiaOneshot Automatic Soap System258257.0
IndonesiaSprouts - Baby Pea Tendrils26054.5
" ], "text/plain": [ "[('Indonesia', 'Beef - Rib Eye Aaa', 3, 60284.333333333336),\n", " ('China', 'Coke - Diet, 355 Ml', 2, 46720.0),\n", " ('Indonesia', 'Cheese - La Sauvagine', 2, 54260.5),\n", " ('Indonesia', 'Fennel - Seeds', 2, 36930.0),\n", " ('Indonesia', 'Oneshot Automatic Soap System', 2, 58257.0),\n", " ('Indonesia', 'Sprouts - Baby Pea Tendrils', 2, 6054.5)]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "\n", "-- Finding the frequently bought products in China, Indonesia, and Poland.\n", "\n", "SELECT region_name, product_name, COUNT(*) AS num_transactions, AVG(transaction_amount) AS avg_transaction_amount\n", " FROM sales_details\n", " WHERE transaction_amount >= 0 AND (region_name = 'China' OR region_name = 'Indonesia' OR region_name = 'Poland')\n", " GROUP BY region_name, product_name\n", "HAVING COUNT(*) > 1\n", " ORDER BY num_transactions DESC, region_name, product_name;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "3. For example, in China, we always have to ensure to have the `Coke - Diet, 355 Ml` in stock and in Indonesia, `Beef - Rib Eye Aaa`. Notice that, Poland is not in the top 3 sales because some products are bought very frequently. Maybe the products there are just expensive." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "# Finding Promotion Days\n" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite://\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
region_nametime_day_of_weeknum_transactionstotal_transaction_amount
China314687399
China414578406
China513607495
China714702672
Indonesia513717903
" ], "text/plain": [ "[('China', 3, 14, 687399),\n", " ('China', 4, 14, 578406),\n", " ('China', 5, 13, 607495),\n", " ('China', 7, 14, 702672),\n", " ('Indonesia', 5, 13, 717903)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Finding the days of the week where the number of transactions is greater or equal to `10` in China, Indonesia, and Poland.\n", " \n", "SELECT region_name, time_day_of_week, COUNT(*) AS num_transactions, SUM( transaction_amount) as total_transaction_amount\n", " FROM sales_details\n", " WHERE transaction_amount >= 0 AND (region_name = 'China' OR region_name = 'Indonesia' OR region_name = 'Poland' )\n", " GROUP BY region_name, time_day_of_week\n", "HAVING COUNT(*) >= 10\n", " ORDER BY region_name, time_day_of_week;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "2. In China most of the days are interesting while in Indonesia it is only on Friday. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.6" } }, "nbformat": 4, "nbformat_minor": 2 }