<jupyter_start><jupyter_text>"Answering Business Questions for an Online Music Store using SQL"> "I use intermediate SQL techniques like views, joins, aggregations, and set operations in order to solve 4 scenarios about a hypothetical online music store. Results are communicated with Matplotlib and Altair visualizations."- author: <NAME>- toc: true- branch: master- badges: true- comments: true- categories: [sql, sqlite, python, pandas, numpy, matplotlib, seaborn, altair]- hide: false- search_exclude: false- image: images/2021-07-31-music-shop.jfif Unsplash | Clay Banks  OverviewThe [Chinook database](https://github.com/lerocha/chinook-database) by <NAME> and <NAME> is a sample database about a hypothetical digital media store called Chinook. This store sells individual music tracks online, similar to iTunes. The database contains tables covering various aspects of the company, such as the employees, customers, invoices, tracks, albums, and artists.The schema below, which was designed by Dataquest, lists the columns under each table. Columns connected by lines contain **matching information**. Dataquest Guided Project: Answering Business Questions Using SQL The matching columns allow us to perform joins on these tables. Thus, we are able to answer more complicated questions about the data.In our hypothetical scenario, the Chinook company has requested us to answer the following business questions:- What are the best-selling music genres with regards to USA customers? Based on this, which new albums should be purchased for the Chinook store?- Which of Chinook's sales support agents has the highest total sales from their assigned customers? Can the exemplary performance of these employees be explained by any information in the database?- What are the statistics on the customers and sales for each country where Chinook offers its service?- How many purchases are full albums, and how many are selected sets of tracks? Based on this, what strategy should Chinook adopt when buying new tracks from record companies?SQL will be used to answer all of these questions. Matplotlib and Altair will also be used to produce helpful visualizations. > Note: I wrote this notebook by following a guided project on the [Dataquest](https://www.dataquest.io/) platform, specifically the [Guided Project: Answering Business Questions Using SQL](https://app.dataquest.io/c/46/m/191/guided-project%3A-answering-business-questions-using-sql/1/introduction-and-schema-diagram). The general project flow and research questions came from Dataquest. However, the text and code here are written by me unless stated otherwise.  Preparations Install the necessary packages.<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import altair as alt<jupyter_output><empty_output><jupyter_text>Connect to the database using SQLite.<jupyter_code>%load_ext sql
%sql sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db<jupyter_output><empty_output><jupyter_text>Analysis Tables and ViewsFirst, we'll inspect the tables and views available in the `chinook.db` database.- Tables contain columns of data. Each column has a different name and data type.- Views do not contain data. Instead, these are pre-written SQL queries which show a transformation of existing data. Thus, it can be called a "virtual table." ([Sławińska 2020](https://learnsql.com/blog/sql-view/))<jupyter_code>%%sql
SELECT
    name,
    type
FROM sqlite_master
WHERE type IN ("table", "view")
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.<jupyter_text>Originally, there were only 11 tables and 0 views in the database. The views listed above are ones which I made throughout this project. I will show how I created these views in later sections.For now, let's inspect the `customer` table, as we will be using it to answer most of the company's business questions.<jupyter_code>%%sql
SELECT *
FROM customer
LIMIT 5
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.<jupyter_text>Each row in this table contains data on a different customer of Chinook. Each customer has a unique customer ID and an assigned support representative from Chinook. The support rep's employee ID is stored in the `support_rep_id` column. The other columns contain information on the customer's name, occupation, location, and contact details.> Note: All names and personal details in the Chinook database are fictitious and randomly generated. Public use of this database is not a breach of data privacy.  Best-Selling Music Genres in the USAIn our first scenario, Chinook has signed a deal with a new record company, so its tracks can now be put up for sale on the Chinook store. The record company has 4 albums so far; below are the artist names and their genres.- Regal (Hip-Hop)- Red Tone (Punk)- Meteor and the Girls (Pop)- Slim Jim Bites (Blues)However, Chinook would like to spread its releases over time, so it will only add 3 albums to the store. Thus, we have to determine the best-selling genres on the store. Furthermore, since the record company would like to target a USA audience, we can narrow our analysis to Chinook's USA customers.First, we create a view called `usa_track_purchases`. This will show the genre, track name, unit price, and quantity bought for each of the invoice lines of USA customers.<jupyter_code>%%sql
DROP VIEW IF EXISTS usa_track_purchases;

CREATE VIEW usa_track_purchases AS
    SELECT
        il.invoice_line_id AS invoice_line_id,
        g.name AS genre,
        t.name AS track_name,
        il.unit_price AS unit_price,
        il.quantity AS quantity
    FROM customer AS c
    INNER JOIN
        invoice AS iv
        ON iv.customer_id = c.customer_id
    INNER JOIN
        invoice_line AS il
        ON il.invoice_id = iv.invoice_id
    INNER JOIN
        track AS t
        ON t.track_id = il.track_id
    INNER JOIN
        genre AS g
        ON g.genre_id = t.genre_id
    WHERE c.country = "USA"
;

SELECT *
FROM usa_track_purchases
LIMIT 7
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>By querying the view above, we will create another view, `usa_genre_sales`. This will contain the following specific information about each genre:- Number of tracks sold- Percentage of tracks sold- Total sales in US dollars<jupyter_code>%%sql 
DROP VIEW IF EXISTS usa_genre_sales;

CREATE VIEW usa_genre_sales AS
    SELECT
        genre,
        
        SUM(quantity) AS number_sold,
        
        --Get the quantity per genre and divide it by the total quantity of USA purchases.
        ROUND(
            CAST(SUM(quantity) AS Float)
            / CAST(
                (SELECT COUNT(*) FROM usa_track_purchases)
                AS Float
            )
            * 100.0,
            2
        ) AS percentage_sold,
        
        ROUND(
            SUM(unit_price * CAST(quantity AS Float)),
            2
        ) AS total_sales
        
    FROM usa_track_purchases
    GROUP BY genre
    ORDER BY number_sold DESC, total_sales DESC
;
%%sql result <<
SELECT *
FROM usa_genre_sales
;
usa_genre_df = result.DataFrame()
usa_genre_df<jupyter_output><empty_output><jupyter_text>We can make a bar graph from this result in order to communicate findings better.<jupyter_code>#collapse-hide
(
    alt.Chart(usa_genre_df)
    .mark_bar()
    .encode(
        x = alt.X("genre:N", title = "Music Genre", sort = "-y"),
        y = alt.Y("percentage_sold:Q", title = "Percentage of All Purchases in the USA"),
        color = alt.Color("total_sales:Q", title = "Total Sales (USD)"),
        tooltip = usa_genre_df.columns.tolist(),
    )
    .properties(
        title = "Popularity of Music Genres with Chinook's USA Customers",
        height = 300,
        width = 600,
    )
    .configure_axis(
        labelAngle = 30,
    )
    .interactive()
)<jupyter_output><empty_output><jupyter_text>One can hover over each bar in the chart above for a tooltip with more specific information.Results show that Rock is the best-selling music genre as it makes up 53% of total purchases in the USA. Rock is followed by the Alternative & Punk and Metal genres, which each make up over 10% of purchases.On a side note, the `total_sales` column's values are very close to that of the `number_sold` column. This can be explained by the fact that track prices range from USD 0.99 to USD 1.99.<jupyter_code>%%sql
SELECT
    MIN(unit_price) AS min_price,
    MAX(unit_price) AS max_price
FROM track
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.<jupyter_text>Since there is little variation among track prices, the genre with the most units sold is usually also the genre with the highest sales.Going back to the scenario at hand, we need to compare the statistics on the Hip-Hop, Punk, Pop, and Blues genres. We will run a query for this below.<jupyter_code>%%sql
SELECT *
FROM usa_genre_sales
WHERE genre IN ("Hip Hop/Rap", "Alternative & Punk", "Pop", "Blues")
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.<jupyter_text>The result above shows that Alternative & Punk, Blues, and Pop are the three best-selling genres out of the four. Notably, Alternative & Punk makes up 12.37% of all purchases in the USA.Therefore, we would recommend the Chinook administration to add the albums of **Red Tone (Punk), Slim Jim Bites (Blues), and Meteor and the Girls (Pop)** to the digital store. The album of Regal (Hip-Hop) has lower priority and can be added at a later date.  Sales Support Agent PerformanceNext, Chinook is requesting us to evaluate the performance of its sales support agents. Each customer is assigned to an agent after their first purchase. Since there are only 3 agents, each agent provides support to many customers. Details about the agents are shown in the query below.<jupyter_code>%%sql
SELECT *
FROM employee
WHERE title = "Sales Support Agent"
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.<jupyter_text>Since we have data on each customer's purchases, we can calculate the total purchases associated with each support agent. We can then use this to compare the performance of the agents.First, we create a view called `agent_customer` by joining the `employee` table with the `customer` table based on the support representative ID. We will include some extra details about each agent, such as their birthdate and hire date. We won't include their location since we know that they are all in Calgary, AB, Canada. As for the customers, we will include their customer ID, name, and total purchases as the sum of their invoices.<jupyter_code>%%sql
DROP VIEW IF EXISTS agent_customer;

CREATE VIEW agent_customer AS
    SELECT
        e.employee_id AS agent_id,
        e.first_name || " " || e.last_name AS agent_name,
        e.birthdate AS agent_bd,
        e.hire_date AS agent_hire_date,
        c.customer_id AS customer_id,
        c.first_name || " " || c.last_name AS customer_name,
        SUM(iv.total) AS customer_total_purchases
    FROM employee AS e
    LEFT JOIN
        customer AS c
        ON c.support_rep_id = e.employee_id
    LEFT JOIN
        invoice AS iv
        ON iv.customer_id = c.customer_id
    WHERE e.title = "Sales Support Agent"
    GROUP BY c.customer_id
    ORDER BY agent_id, customer_id
;
%%sql result <<
SELECT *
FROM agent_customer
;
agent_customer_df = result.DataFrame()
agent_customer_df.head()<jupyter_output><empty_output><jupyter_text>Next, we will query this view in order to determine the following information about each agent:- number of customers- total sales from the agent's customers- percentage of all agents' sales- average sales per customer<jupyter_code>%%sql
DROP VIEW IF EXISTS agent_stats;

CREATE VIEW agent_stats AS
    SELECT
        agent_id,
        agent_name,
        agent_bd,
        agent_hire_date,
        COUNT(customer_id) AS number_customers,
        ROUND(
            SUM(customer_total_purchases),
            2
        ) AS sales_number,
        ROUND(
            SUM(customer_total_purchases) 
            / (SELECT SUM(customer_total_purchases) FROM agent_customer)
            * 100,
            2
        )AS sales_percentage,
        ROUND(
            AVG(customer_total_purchases),
            2
        ) AS average_per_customer
    FROM agent_customer
    GROUP BY agent_id
;

SELECT *
FROM agent_stats
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>At first glance, <NAME> seems like the best-performing agent since Chinook got the highest total sales from her customers (USD 1731.51). This idea is cast into doubt when it is mentioned that she had the highest number of customers (21).Also, the differences among the average sales per customer is quite small. Each of Jane's customers spends 3 more dollars on Chinook than each of Margaret's. Each of Margaret's customers spends 2 more dollars on Chinook than each of <NAME>'s.The average sales per customer may also be influenced by outliers, as shown by the boxplot below.<jupyter_code>#collapse-hide
(
    alt.Chart(agent_customer_df)
    .mark_boxplot()
    .encode(
        y = alt.Y("customer_total_purchases:Q", title = "Customer Total Purchases"),
        x = alt.X("agent_name:N", title = "Agent Name"),
    )
    .properties(
        title = "Distribution of Customer Purchases by Sales Support Agent",
        height = 300,
        width = 500,
    )
    .interactive()
)<jupyter_output><empty_output><jupyter_text>If we hover over each of the boxes above, we can get more information such as minimum, 1st quartile, median, etc. The median is less influenced by outliers, so we can look at that. The median values are 79.20 (Jane), 77.72 (Margaret), and 75.74 (Steve). These values are still very close to each other.Therefore, we cannot conclusively state that any agent performs better than the others.  Sales Data by CountryNext, Chinook is requesting us to analyze sales in each country where it offers its service. Specifically, they would like to know the:- total number of customers- total value of sales- average sales per customer- average order value    - Every order is a batch purchase of multiple tracks.    Furthermore, since there are some countries in the database with only one customer, we shall group these customers together under a category called "Other", which must appear at the very bottom of our final result.First, we will create a view, `country_invoices`, which shows all invoices and the country of the customer.<jupyter_code>%%sql
DROP VIEW IF EXISTS country_invoices;

CREATE VIEW country_invoices AS
    SELECT
        c.country AS country,
        c.customer_id AS customer_id,
        c.first_name || " " || c.last_name AS customer_name,
        iv.invoice_id AS invoice_id,
        iv.total AS order_value
    FROM customer AS c
    LEFT JOIN
        invoice AS iv
        ON iv.customer_id = c.customer_id
;

SELECT *
FROM country_invoices
LIMIT 5
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>Then, we will create a view, `country_customers`, which shows the total purchases per customer.<jupyter_code>%%sql
DROP VIEW IF EXISTS country_customers;

CREATE VIEW country_customers AS
    SELECT
        country,
        customer_id,
        customer_name,
        SUM(order_value) AS customer_total_purchase
    FROM country_invoices
    GROUP BY customer_id
;

SELECT *
FROM country_customers
LIMIT 5
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>We will then create a view called `country_labels`. It will show the number of customers per country. Countries with only 1 customer will be given a label of "Other".<jupyter_code>%%sql
DROP VIEW IF EXISTS country_labels;

CREATE VIEW country_labels AS
    SELECT
        country,
        COUNT(customer_id) AS number_customers,
        CASE
            WHEN COUNT(customer_id) > 1 THEN country
            ELSE "Other"
            END AS country_label,
        CASE
            WHEN COUNT(customer_id) > 1 THEN 0
            ELSE 1
            END AS is_other
    FROM country_customers
    GROUP BY country
;

SELECT *
FROM country_labels
LIMIT 10
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>We will also create a view called `country_avg_order` which simply shows the average order value per country. This will be done by querying the `country_invoices` view.<jupyter_code>%%sql
DROP VIEW IF EXISTS country_avg_order;

CREATE VIEW country_avg_order AS
    SELECT
        cl.country_label,
        AVG(civ.order_value) AS avg_order_value
    FROM country_invoices AS civ
    INNER JOIN
        country_labels AS cl
        ON cl.country = civ.country
    GROUP BY cl.country_label
;

SELECT *
FROM country_avg_order
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>Finally, we will create a view called `country_stats` that shows all of the 4 statistics that were requested by the Chinook management. The "Other" entry will be forced to the bottom of the query result using the `is_other` column we created in the `country_labels` view.<jupyter_code>%%sql
DROP VIEW IF EXISTS country_stats;

CREATE VIEW country_stats AS
    SELECT
        l.country_label,
        COUNT(c.customer_id) AS number_customers,
        ROUND(SUM(c.customer_total_purchase), 2) AS total_sales,
        ROUND(AVG(c.customer_total_purchase), 2) AS avg_sales_customer,
        ROUND(a.avg_order_value, 2) AS avg_order_value
    FROM country_customers AS c
    INNER JOIN
        country_labels AS l
        ON l.country = c.country
    INNER JOIN
        country_avg_order AS a
        ON a.country_label = l.country_label
    GROUP BY l.country_label
    ORDER BY
        l.is_other ASC,
        total_sales DESC
;
%%sql result <<
SELECT *
FROM country_stats
;
country_stats_df = result.DataFrame()
country_stats_df<jupyter_output><empty_output><jupyter_text>We can make a scatter plot with the data above in order to make results more clear.<jupyter_code>#collapse-hide
# Exclude the "Other" entry.
main_df = country_stats_df.loc[country_stats_df["country_label"] != "Other"]

# Base layer.
base = (
    alt.Chart(main_df)
    .mark_point(size = 300) # Set default size of points to 300 pixels.
    .encode(
        x = alt.X("number_customers:Q", title = "Number of Customers"),
        y = alt.Y("total_sales:Q", title = "Total Sales (USD)"),
    )
)

# Scatter plot layer.
points = base.encode(
    color = alt.Color("avg_sales_customer:Q", title = "Average Sales per Customer (USD)"),
    tooltip = country_stats_df.columns.tolist(),
)

# Text layer.
text = (
    base
    .mark_text( # Move text to the top left of each point.
        align = "right",
        dy = -5,
        dx = -20,
    )
    .encode(
        text = "country_label:N"
    )
)

# Combine layers.
chart = (
    (points + text)
    .properties(
        title = "Chinook Sales by Country",
        height = 300,
        width = 700,
    )
    .interactive()
)

# Display chart.
chart<jupyter_output><empty_output><jupyter_text>Looking at the top right of the chart, we can see that the USA has the highest number of Chinook customers (13), as well as the highest total sales (USD 1040.49). All of the other countries have only 2 to 8 customers.On the other hand, the Czech Republic has the highest average sales per customer at USD 136.62; this is indicated by its dark color in the chart. This country also had the highest average order value at USD 9.11. This means that though the country has few customers, these people are avid buyers of music.Chinook may benefit from marketing its service more aggressively in countries other than the USA where it has gained a good foothold, such as **Canada, Brazil, and France**. Chinook may also target the **Czech Republic** since the customers there seem to buy a lot of music on a per-person basis.  Comparing Purchase Types: Full Album vs Selected SetsIn our last scenario, we have been requested to compare the popularities of Chinook's two purchase types:- Full album    - The customer buys a full album.    - Albums are pre-defined in Chinook's library. The customer may not add other tracks on top of an album.- Selected set of tracks    - The customer manually selects any number of individual tracks.In both cases, each track is bought at its unit price; there are no discounts.Currently, Chinook's purchasing strategy is to **buy full albums** from record companies. However, Chinook doesn't know whether full album purchases are popular among its customers. If selected sets are more popular, then Chinook may switch to a new strategy in which it will only **buy the most popular individual tracks** from record companies.Our analysis will help Chinook make the final decision. For each purchase type, we will show the number of invoices and percentage of all invoices with that type. The type with the higher number will be the more popular one.First, we create a view, `invoice_tracks`, that shows all tracks under each invoice. The album associated with each track is also shown.<jupyter_code>%%sql
DROP VIEW IF EXISTS invoice_tracks_bought;

CREATE VIEW invoice_tracks_bought AS
    SELECT
        iv.invoice_id AS invoice_id,
        il.track_id AS track_id,
        t.name AS track_name,
        t.album_id AS album_id,
        a.title AS album_name
    FROM invoice AS iv
    INNER JOIN
        invoice_line AS il
        ON il.invoice_id = iv.invoice_id
    INNER JOIN
        track AS t
        ON t.track_id = il.track_id
    INNER JOIN
        album AS a
        ON a.album_id = t.album_id
    ORDER BY invoice_id, track_id, album_id
;

SELECT *
FROM invoice_tracks_bought
LIMIT 7
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>Then, we create a view called `invoice_album`, which shows only the album associated with the first track of each invoice. This way, there is only **one row per invoice**. We will also include the total purchase amount of each invoice so that we can use it later.<jupyter_code>%%sql
DROP VIEW IF EXISTS invoice_album;

CREATE VIEW invoice_album AS 
    SELECT
        itb.invoice_id AS invoice_id,
        MIN(itb.album_id) AS album_id,
        itb.album_name AS album_name,
        iv.total AS total_purchase
    FROM invoice_tracks_bought AS itb
    INNER JOIN
        invoice AS iv
        ON iv.invoice_id = itb.invoice_id
    GROUP BY itb.invoice_id
    ORDER BY itb.invoice_id
;

SELECT *
FROM invoice_album
LIMIT 5
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>Then, we join `invoice_album` with the `track` table in order to **list all of the tracks** under the album associated with each invoice. The result will be a view called `invoice_full_album_tracks`.<jupyter_code>%%sql
DROP VIEW IF EXISTS invoice_full_album_tracks;

CREATE VIEW invoice_full_album_tracks AS
    SELECT
        ia.*,
        t.track_id AS track_id,
        t.name AS track_name
    FROM invoice_album AS ia
    INNER JOIN
        track AS t
        ON t.album_id = ia.album_id
    ORDER BY invoice_id, album_id, track_id
;

SELECT *
FROM invoice_full_album_tracks
LIMIT 5
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>The `invoice_full_album_tracks` view looks very similar to the `invoice_tracks_bought` view at first glance. However, there is a difference:- `invoice_tracks_bought`    - It contains the tracks **bought** in each invoice.    - Each set of tracks may or may not be a full album.- `invoice_full_album_tracks`    - It contains all of the tracks under one album associated with an invoice.    - Some of these tracks **may not have been bought** by the customer.    Next, we will create a new view called `invoice_purchase_type` which indicates whether each invoice is a "Full Album" or "Selected Set" purchase.In order to determine this, we will have a `CASE` statement which can be explained as follows:- `WHEN` clause: If the set of tracks bought and the full album are **exactly the same**, mark the purchase type as "Full Album".- `ELSE` clause: Otherwise, mark the purchase type as "Selected Set".Inside the `WHEN` clause, we have a rather complicated-looking set of operations. Let's look at one part:    (        SELECT itb.track_id        FROM invoice_tracks_bought AS itb        WHERE itb.invoice_id = ia.invoice_id        EXCEPT        SELECT ifa.track_id        FROM invoice_full_album_tracks AS ifa        WHERE ifa.invoice_id = ia.invoice_id    ) IS NULL    In order to make the explanation more simple, we can call the subqueries above "set 1" and "set 2".    (        {set 1}        EXCEPT        {set 2}    ) IS NULL    - Set 1 represents all tracks bought in one invoice.- Set 2 represents the full set of tracks in an album associated with the invoice.- Via `EXCEPT` and `IS NULL`, we check whether Set 1 is a subset of Set 2.    - If it is, the result is True.    - Otherwise, False.We then repeat the same process but *in reverse*, to check if Set 2 is a subset of Set 1. We thus end up with *two boolean values*, and we use the `AND` operator on these.    (        {set 1}        EXCEPT        {set 2}    ) IS NULL    AND    (        {set 2}        EXCEPT        {set 1}    ) IS NULLThe purpose of `AND` is to determine the following.- If both conditions are True:    - The two sets of tracks match exactly.    - The invoice is a "Full Album" purchase.- If any condition is False:    - The two sets of tracks do not match exactly.    - The invoice is a "Selected Set" purchase.    The full query is shown below.<jupyter_code>%%sql
DROP VIEW IF EXISTS invoice_purchase_type;

CREATE VIEW invoice_purchase_type AS
    SELECT
        ia.invoice_id,
        CASE
            WHEN (

                (
                    SELECT itb.track_id
                    FROM invoice_tracks_bought AS itb
                    WHERE itb.invoice_id = ia.invoice_id

                    EXCEPT

                    SELECT ifa.track_id
                    FROM invoice_full_album_tracks AS ifa
                    WHERE ifa.invoice_id = ia.invoice_id
                ) IS NULL

                AND

                (
                    SELECT ifa.track_id
                    FROM invoice_full_album_tracks AS ifa
                    WHERE ifa.invoice_id = ia.invoice_id

                    EXCEPT

                    SELECT itb.track_id
                    FROM invoice_tracks_bought AS itb
                    WHERE itb.invoice_id = ia.invoice_id
                ) IS NULL

            ) THEN "Full Album"
            ELSE "Selected Set"
            END AS purchase_type,
        ia.total_purchase AS total_purchase
    FROM invoice_album AS ia
;

SELECT *
FROM invoice_purchase_type
LIMIT 10
;<jupyter_output>* sqlite:///private/2021-07-31-Intermediate-SQL-Files/chinook.db
Done.
Done.
Done.<jupyter_text>With the view above, we can finally answer the question being asked. A view called `purchase_type_proportion` will be created which shows the following information per purchase type:- number of invoices- percentage of invoices- average sales per invoice- sales in USD- percentage of total sales<jupyter_code>%%sql
DROP VIEW IF EXISTS purchase_type_proportion;

CREATE VIEW purchase_type_proportion AS
    SELECT
        purchase_type,
        
        COUNT(purchase_type) AS type_count,
        
        ROUND(
            CAST(COUNT(purchase_type) AS Float)
            / CAST(
                (SELECT COUNT(purchase_type)
                 FROM invoice_purchase_type)
                AS Float
            )
            * 100,
            2
        ) AS type_percentage,
        
        ROUND(
            AVG(total_purchase),
            2
        ) AS avg_sales_per_invoice,
        
        ROUND(
            SUM(total_purchase),
            2
        ) AS sales_number,
        
        ROUND(
            SUM(total_purchase)
            / (SELECT SUM(total_purchase)
               FROM invoice_purchase_type)
            * 100,
            2
        ) AS sales_percentage
        
    FROM invoice_purchase_type
    GROUP BY purchase_type
    ORDER BY type_count DESC
;
%%sql result <<
SELECT *
FROM purchase_type_proportion
;
purchase_type_df = result.DataFrame()
purchase_type_df<jupyter_output><empty_output><jupyter_text>The `type_percentage` column shows that the majority (81%) of all Chinook invoices are Selected Sets. Full Album purchases only make up 19% of all invoices.This is shown in the pie chart below.<jupyter_code>#collapse-hide
colors = sns.color_palette('pastel')[:2]

plt.pie(
    x = purchase_type_df["type_percentage"],
    labels = purchase_type_df["purchase_type"],
    colors = colors,
    autopct = "%0.2f%%",
)

plt.title("Proportion of Chinook Store Invoices by Purchase Type")

plt.show()<jupyter_output><empty_output><jupyter_text>However, the average sales per Full Album purchase is *almost twice* that of a Selected Set purchase. This is shown in the bar chart below.<jupyter_code>#collapse-hide
# Base layer with bar chart
base = (
    alt.Chart(purchase_type_df)
    .mark_bar()
    .encode(
        x = alt.X("purchase_type:N", title = "Purchase Type"),
        y = alt.Y("avg_sales_per_invoice:Q", title = "Average Sales per Invoice (USD)"),
        tooltip = purchase_type_df.columns.tolist(),
    )
)

# Text layer
text = (
    base
    .mark_text(
        align = "center",
        dy = 10,
        color = "white",
    )
    .encode(
        text = "avg_sales_per_invoice:Q",
    )
)

# Combine layers into one chart
chart = (
    (base + text)
    .properties(
        title = "Average Sales per Invoice by Purchase Type",
        height = 300,
        width = 200,
    )
    .configure_axis(labelAngle = 30)
    .interactive()
)

# Display chart.
chart<jupyter_output><empty_output><jupyter_text>Full Albums cost more than Selected Sets because the former tend to have more tracks. Thus, even if Full Albums only represent 19% of all invoices, these also represent *31% of all dollar sales*. This is shown in the pie chart below.<jupyter_code>#collapse-hide
plt.pie(
    x = purchase_type_df["sales_percentage"],
    labels = purchase_type_df["purchase_type"],
    colors = colors,
    autopct = "%0.2f%%",
)

plt.title("Proportion of Chinook Store Sales by Purchase Type")

plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06845480290355131, 'fraction_numerical': 0.010318674522313727, 'mean_word_length': 3.1935571935571936, 'pattern_counts': {'":': 0, '<': 107, '<?xml version=': 0, '>': 104, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '665384', 'license': 'Apache-2.0', 'n_tokens_mistral': 9367, 'n_tokens_neox': 8684, 'n_words': 3581, 'path': '_notebooks/2021-07-31-Answering-Business-Questions-Online-Music-Store-SQL.ipynb', 'repo_name': 'MiguelAHG/mahg-data-science', 'size': '30750'}