For this set of exercises, we'll use the Chinook database, downloaded for SQLite from this website.
The four exercises are detailed below.
%%capture
%load_ext sql
%sql sqlite:///../Datasets/chinook.db
%%sql
SELECT *
FROM sqlite_master
WHERE type='table';
* sqlite:///../Datasets/chinook.db Done.
type | name | tbl_name | rootpage | sql |
---|---|---|---|---|
table | album | album | 2 | CREATE TABLE [album] ( [album_id] INTEGER PRIMARY KEY NOT NULL, [title] NVARCHAR(160) NOT NULL, [artist_id] INTEGER NOT NULL, FOREIGN KEY ([artist_id]) REFERENCES [artist] ([artist_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | artist | artist | 3 | CREATE TABLE [artist] ( [artist_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | customer | customer | 4 | CREATE TABLE [customer] ( [customer_id] INTEGER PRIMARY KEY NOT NULL, [first_name] NVARCHAR(40) NOT NULL, [last_name] NVARCHAR(20) NOT NULL, [company] NVARCHAR(80), [address] NVARCHAR(70), [city] NVARCHAR(40), [state] NVARCHAR(40), [country] NVARCHAR(40), [postal_code] NVARCHAR(10), [phone] NVARCHAR(24), [fax] NVARCHAR(24), [email] NVARCHAR(60) NOT NULL, [support_rep_id] INTEGER, FOREIGN KEY ([support_rep_id]) REFERENCES [employee] ([employee_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | employee | employee | 5 | CREATE TABLE [employee] ( [employee_id] INTEGER PRIMARY KEY NOT NULL, [last_name] NVARCHAR(20) NOT NULL, [first_name] NVARCHAR(20) NOT NULL, [title] NVARCHAR(30), [reports_to] INTEGER, [birthdate] DATETIME, [hire_date] DATETIME, [address] NVARCHAR(70), [city] NVARCHAR(40), [state] NVARCHAR(40), [country] NVARCHAR(40), [postal_code] NVARCHAR(10), [phone] NVARCHAR(24), [fax] NVARCHAR(24), [email] NVARCHAR(60), FOREIGN KEY ([reports_to]) REFERENCES [employee] ([employee_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | genre | genre | 6 | CREATE TABLE [genre] ( [genre_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | invoice | invoice | 7 | CREATE TABLE [invoice] ( [invoice_id] INTEGER PRIMARY KEY NOT NULL, [customer_id] INTEGER NOT NULL, [invoice_date] DATETIME NOT NULL, [billing_address] NVARCHAR(70), [billing_city] NVARCHAR(40), [billing_state] NVARCHAR(40), [billing_country] NVARCHAR(40), [billing_postal_code] NVARCHAR(10), [total] NUMERIC(10,2) NOT NULL, FOREIGN KEY ([customer_id]) REFERENCES [customer] ([customer_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | invoice_line | invoice_line | 8 | CREATE TABLE [invoice_line] ( [invoice_line_id] INTEGER PRIMARY KEY NOT NULL, [invoice_id] INTEGER NOT NULL, [track_id] INTEGER NOT NULL, [unit_price] NUMERIC(10,2) NOT NULL, [quantity] INTEGER NOT NULL, FOREIGN KEY ([invoice_id]) REFERENCES [invoice] ([invoice_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([track_id]) REFERENCES [track] ([track_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | media_type | media_type | 9 | CREATE TABLE [media_type] ( [media_type_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | playlist | playlist | 10 | CREATE TABLE [playlist] ( [playlist_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(120) ) |
table | playlist_track | playlist_track | 11 | CREATE TABLE [playlist_track] ( [playlist_id] INTEGER NOT NULL, [track_id] INTEGER NOT NULL, CONSTRAINT [pk_playlist_track] PRIMARY KEY ([playlist_id], [track_id]), FOREIGN KEY ([playlist_id]) REFERENCES [playlist] ([playlist_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([track_id]) REFERENCES [track] ([track_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
table | track | track | 13 | CREATE TABLE [track] ( [track_id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(200) NOT NULL, [album_id] INTEGER, [media_type_id] INTEGER NOT NULL, [genre_id] INTEGER, [composer] NVARCHAR(220), [milliseconds] INTEGER NOT NULL, [bytes] INTEGER, [unit_price] NUMERIC(10,2) NOT NULL, FOREIGN KEY ([album_id]) REFERENCES [album] ([album_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([genre_id]) REFERENCES [genre] ([genre_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([media_type_id]) REFERENCES [media_type] ([media_type_id]) ON DELETE NO ACTION ON UPDATE NO ACTION ) |
The Chinook record store has just signed a deal with a new record label, and I've been tasked with selecting the first three albums that will be added to the store from a list of four. All four albums are by artists that don't have any tracks in the store right now - we have the artist names, and the genre of music they produce:
Artist Name | Genre |
---|---|
Regal | Hip-Hop |
Red Tone | Punk |
Meteor and the Girls | Pop |
Slim Jim Bites | Blues |
The record label specializes in artists from the USA, and they have given Chinook some money to advertise the new albums in the USA, so we're interested in finding out which genres sell the best in the USA.
I'll write a query to find out which genres sell the most tracks in the USA, write up a summary of my findings, and make a recommendation for the three artists whose albums we should purchase for the store.
For this query, I'll need to join the track
, invoice_line
, genre
, invoice
, and customer
tables from Chinook.
Since we want to know the quantity of tracks bought, I'll first check whether the "Quantity" column is useful by seeing if there are values other than 1 in it.
%%sql
SELECT DISTINCT Quantity
FROM invoice_line;
* sqlite:///../Datasets/chinook.db Done.
quantity |
---|
1 |
The values of Quantity include only ones. Thus, aggregating a count of the tracks from the invoice_line table will be the same as summing the Quantity column.
So, let's take all tracks from the USA and calculate the total sold by music genre as an absolute number and a percentage. We'll determine country by the customer address rather than the billing address, assuming the customer is the one listening and that the customer country and billing country might not always align.
Note here that I tried INNER and LEFT joins and got the same results. I did this to see whether it would change the low total number of tracks from the US, but it is simply a small sample.
%%sql
WITH
tracks_invoice_line_genre AS
(
SELECT g.name Genre
FROM track t
LEFT JOIN invoice_line ii ON ii.invoice_line_id = t.track_id
LEFT JOIN genre g ON g.genre_id = t.genre_id
LEFT JOIN invoice i ON i.invoice_id = ii.invoice_line_id
LEFT JOIN customer c ON c.customer_id = i.customer_id
WHERE c.country = "USA"
)
SELECT Genre,
COUNT(*) AS Tracks_Sold_abs,
ROUND(
100.0 * COUNT(*) / (
SELECT COUNT(*)
FROM tracks_invoice_line_genre
),
2) AS Tracks_Sold_perc
FROM tracks_invoice_line_genre
GROUP BY Genre
ORDER BY Tracks_Sold_perc DESC;
* sqlite:///../Datasets/chinook.db Done.
Genre | Tracks_Sold_abs | Tracks_Sold_perc |
---|---|---|
Rock | 37 | 28.24 |
Latin | 36 | 27.48 |
Metal | 12 | 9.16 |
Reggae | 11 | 8.4 |
Jazz | 11 | 8.4 |
Alternative & Punk | 11 | 8.4 |
Blues | 5 | 3.82 |
Soundtrack | 4 | 3.05 |
Pop | 3 | 2.29 |
Rock And Roll | 1 | 0.76 |
The previous query used a subquery to calculate the percentage of tracks sold. This can also be done with the window function OVER()
, which allows us to specify over what subset of the data we are aggregating:
%%sql
WITH
tracks_invoice_line_genre AS
(
SELECT g.name Genre
FROM track t
LEFT JOIN invoice_line ii ON ii.invoice_line_id = t.track_id
LEFT JOIN genre g ON g.genre_id = t.genre_id
LEFT JOIN invoice i ON i.invoice_id = ii.invoice_line_id
LEFT JOIN customer c ON c.customer_id = i.customer_id
WHERE c.country = "USA"
)
SELECT Genre,
COUNT(*) AS Tracks_Sold_abs,
ROUND(
100.0 * COUNT(*) / SUM(COUNT(*)) OVER(),
2) AS Tracks_Sold_perc
FROM tracks_invoice_line_genre
GROUP BY Genre
ORDER BY Tracks_Sold_perc DESC;
* sqlite:///../Datasets/chinook.db Done.
Genre | Tracks_Sold_abs | Tracks_Sold_perc |
---|---|---|
Rock | 37 | 28.24 |
Latin | 36 | 27.48 |
Metal | 12 | 9.16 |
Alternative & Punk | 11 | 8.4 |
Jazz | 11 | 8.4 |
Reggae | 11 | 8.4 |
Blues | 5 | 3.82 |
Soundtrack | 4 | 3.05 |
Pop | 3 | 2.29 |
Rock And Roll | 1 | 0.76 |
The genre of our promoted artists above were: Hip-Hop, Punk, Pop, and Blues. In our data set, Hip-Hop does not even appear on the list, automatically allowing us to choose the other three albums to appear in our store. Thus, we will add to our store the albums by Red Tone (Punk), Meteor and the Girls (Pop), and Slim Jim Bites (Blues). Based on the previous tracks sold, we can expect the Punk album by Red Tone to sell the best of these three, followed by the Blues album and Pop album.
Each customer for the Chinook store gets assigned to a sales support agent within the company when they first make a purchase. I have been asked to analyze the purchases of customers belonging to each employee to see if any sales support agent is performing either better or worse than the others.
To do so, I'll look into the total dollar amount of sales that each support agent is responsible for. Here, we'll need the employees
, customer
, and invoice
tables. Let's have a peek at those tables and their variables.
%%sql
SELECT *,
(c.first_name || " " || c.last_name) Customer_Name,
(e.first_name || " " || e.last_name) Employee_Name
FROM employee e
INNER JOIN customer c ON c.support_rep_id = e.employee_id
INNER JOIN invoice i ON i.customer_id = c.customer_id
LIMIT 5;
* sqlite:///../Datasets/chinook.db Done.
employee_id | last_name | first_name | title | reports_to | birthdate | hire_date | address | city | state | country | postal_code | phone | fax | customer_id | first_name_1 | last_name_1 | company | address_1 | city_1 | state_1 | country_1 | postal_code_1 | phone_1 | fax_1 | email_1 | support_rep_id | invoice_id | customer_id_1 | invoice_date | billing_address | billing_city | billing_state | billing_country | billing_postal_code | total | Customer_Name | Employee_Name | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
3 | Peacock | Jane | Sales Support Agent | 2 | 1973-08-29 00:00:00 | 2017-04-01 00:00:00 | 1111 6 Ave SW | Calgary | AB | Canada | T2P 5M5 | +1 (403) 262-3443 | +1 (403) 262-6712 | jane@chinookcorp.com | 18 | Michelle | Brooks | None | 627 Broadway | New York | NY | USA | 10012-2612 | +1 (212) 221-3546 | +1 (212) 221-4679 | michelleb@aol.com | 3 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | Michelle Brooks | Jane Peacock |
3 | Peacock | Jane | Sales Support Agent | 2 | 1973-08-29 00:00:00 | 2017-04-01 00:00:00 | 1111 6 Ave SW | Calgary | AB | Canada | T2P 5M5 | +1 (403) 262-3443 | +1 (403) 262-6712 | jane@chinookcorp.com | 30 | Edward | Francis | None | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | +1 (613) 234-3322 | None | edfrancis@yachoo.ca | 3 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | Edward Francis | Jane Peacock |
4 | Park | Margaret | Sales Support Agent | 2 | 1947-09-19 00:00:00 | 2017-05-03 00:00:00 | 683 10 Street SW | Calgary | AB | Canada | T2P 5G3 | +1 (403) 263-4423 | +1 (403) 263-4289 | margaret@chinookcorp.com | 40 | Dominique | Lefebvre | None | 8, Rue Hanovre | Paris | None | France | 75002 | +33 01 47 42 71 71 | None | dominiquelefebvre@gmail.com | 4 | 3 | 40 | 2017-01-05 00:00:00 | 8, Rue Hanovre | Paris | None | France | 75002 | 1.98 | Dominique Lefebvre | Margaret Park |
3 | Peacock | Jane | Sales Support Agent | 2 | 1973-08-29 00:00:00 | 2017-04-01 00:00:00 | 1111 6 Ave SW | Calgary | AB | Canada | T2P 5M5 | +1 (403) 262-3443 | +1 (403) 262-6712 | jane@chinookcorp.com | 18 | Michelle | Brooks | None | 627 Broadway | New York | NY | USA | 10012-2612 | +1 (212) 221-3546 | +1 (212) 221-4679 | michelleb@aol.com | 3 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | Michelle Brooks | Jane Peacock |
4 | Park | Margaret | Sales Support Agent | 2 | 1947-09-19 00:00:00 | 2017-05-03 00:00:00 | 683 10 Street SW | Calgary | AB | Canada | T2P 5G3 | +1 (403) 263-4423 | +1 (403) 263-4289 | margaret@chinookcorp.com | 27 | Patrick | Gray | None | 1033 N Park Ave | Tucson | AZ | USA | 85719 | +1 (520) 622-4200 | None | patrick.gray@aol.com | 4 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | Patrick Gray | Margaret Park |
%%sql
WITH
employees_customer_invoice AS
(
SELECT *,
(c.first_name || " " || c.last_name) Customer_Name,
(e.first_name || " " || e.last_name) Employee_Name
FROM employee e
INNER JOIN customer c ON c.support_rep_id = e.employee_id
INNER JOIN invoice i ON i.customer_id = c.customer_id
)
SELECT Employee_Name, city, state, country, hire_date, birthdate,
ROUND(SUM(total), 2) Total_customer_purchases
FROM employees_customer_invoice
GROUP BY Employee_Name;
* sqlite:///../Datasets/chinook.db Done.
Employee_Name | city | state | country | hire_date | birthdate | Total_customer_purchases |
---|---|---|---|---|---|---|
Jane Peacock | Calgary | AB | Canada | 2017-04-01 00:00:00 | 1973-08-29 00:00:00 | 1731.51 |
Margaret Park | Calgary | AB | Canada | 2017-05-03 00:00:00 | 1947-09-19 00:00:00 | 1584.0 |
Steve Johnson | Calgary | AB | Canada | 2017-10-17 00:00:00 | 1965-03-03 00:00:00 | 1393.92 |
Jane Peacock seems to handle the largest number of customer purchases by a decent margin. In that sense, she performs better than the others. The determining factor for this could be that she was hired a year earlier than the other two employees listed.
My next task is to analyze the sales data for customers from each different country. I have been given guidance to use the country value from the customers table, and ignore the country from the billing address in the invoice table.
In particular, I have been directed to calculate results for each country on the total number of customers, total value of sales, average value of sales per customer, and average order value. Because there are a number of countries with only one customer, these customers will be grouped as "Other" in my analysis.
For this analysis, we'll need the customer
and invoice
tables. Let's have a peek at those tables and their variables.
%%sql
SELECT *
FROM customer c
INNER JOIN invoice i ON i.customer_id = c.customer_id
LIMIT 5;
* sqlite:///../Datasets/chinook.db Done.
customer_id | first_name | last_name | company | address | city | state | country | postal_code | phone | fax | support_rep_id | invoice_id | customer_id_1 | invoice_date | billing_address | billing_city | billing_state | billing_country | billing_postal_code | total | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
18 | Michelle | Brooks | None | 627 Broadway | New York | NY | USA | 10012-2612 | +1 (212) 221-3546 | +1 (212) 221-4679 | michelleb@aol.com | 3 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 |
30 | Edward | Francis | None | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | +1 (613) 234-3322 | None | edfrancis@yachoo.ca | 3 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 |
40 | Dominique | Lefebvre | None | 8, Rue Hanovre | Paris | None | France | 75002 | +33 01 47 42 71 71 | None | dominiquelefebvre@gmail.com | 4 | 3 | 40 | 2017-01-05 00:00:00 | 8, Rue Hanovre | Paris | None | France | 75002 | 1.98 |
18 | Michelle | Brooks | None | 627 Broadway | New York | NY | USA | 10012-2612 | +1 (212) 221-3546 | +1 (212) 221-4679 | michelleb@aol.com | 3 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 |
27 | Patrick | Gray | None | 1033 N Park Ave | Tucson | AZ | USA | 85719 | +1 (520) 622-4200 | None | patrick.gray@aol.com | 4 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 |
First, I'll create a view with customers by country calculated.
%%sql
DROP VIEW IF EXISTS customers_by_country;
CREATE VIEW customers_by_country AS
SELECT country,
COUNT(*) total_customers
FROM customer
GROUP BY country
ORDER by total_customers;
* sqlite:///../Datasets/chinook.db Done. Done.
[]
Now I'll perform the query to get the data by country. customers_by_country_other
will be our sorting table, customer_invoice
our main table to take variables from, and sales_per_customer
our view to take the sales per customer from.
%%sql
WITH customers_by_country_other AS
(
SELECT *,
CASE
WHEN total_customers = 1 THEN "Other"
ELSE country
END AS country_other,
CASE
WHEN total_customers = 1 THEN 0
ELSE 1
END AS country_sort
FROM customers_by_country
),
customer_invoice AS
(
SELECT *,
(c.first_name || " " || c.last_name) Customer_Name
FROM customer c
INNER JOIN invoice i ON i.customer_id = c.customer_id
),
sales_per_customer AS
(
SELECT customer_id,
ROUND(SUM(total) / COUNT(total), 2) AS customer_average
FROM customer_invoice
GROUP BY customer_id
)
SELECT cbco.country_other AS country,
COUNT(DISTINCT ci.Customer_Name) AS total_customers,
ROUND(SUM(ci.total), 2) AS total_sales,
spc.customer_average,
ROUND(SUM(ci.total) / COUNT(ci.total), 2) AS average_order_value
FROM customer_invoice ci
INNER JOIN customers_by_country_other cbco ON cbco.country = ci.country
INNER JOIN sales_per_customer spc ON spc.customer_id = ci.customer_id
GROUP BY cbco.country_other
ORDER BY cbco.country_sort DESC, average_order_value DESC;
* sqlite:///../Datasets/chinook.db Done.
country | total_customers | total_sales | customer_average | average_order_value |
---|---|---|---|---|
Czech Republic | 2 | 273.24 | 8.03 | 9.11 |
United Kingdom | 3 | 245.52 | 8.54 | 8.77 |
India | 2 | 183.15 | 8.61 | 8.72 |
Germany | 4 | 334.62 | 7.47 | 8.16 |
USA | 13 | 1040.49 | 9.28 | 7.94 |
France | 5 | 389.07 | 8.8 | 7.78 |
Canada | 8 | 535.59 | 11.11 | 7.05 |
Brazil | 5 | 427.68 | 8.38 | 7.01 |
Portugal | 2 | 185.13 | 7.92 | 6.38 |
Other | 15 | 1094.94 | 8.03 | 7.45 |
Each of our returned variables provides us with some information. Note that it is helpful to look at each variable individually by sorting on that variable.
The largest number of customers from a single country are from the USA, followed by Canada. In total gross sales, the USA also leads, which is unsurprising given that they have the largest number of customers. The average total sales per customer is highest in Canada, followed by the USA. The average order value is highest in the Czech Republic, followed by the United Kingdom.
The Chinook store is set up in a way that allows customers to make purchases in one of two ways: purchase a whole album, or purchase a collection of one or more individual tracks.
The store does not let customers purchase a whole album and then add individual tracks to that same purchase (unless they do that by choosing each track manually). When customers purchase albums they are charged the same price as if they had purchased each of those tracks separately.
Management are currently considering changing their purchasing strategy to save money. The strategy they are considering is to purchase only the most popular tracks from each album from record companies, instead of purchasing every track from an album.
I have been asked to find out what percentage of purchases are of individual tracks vs whole albums so that management can use this data to understand the effect that this decision might have on overall revenue.
To do so, we'll need to write a query that categorizes each invoice as either an album purchase or not and calculates the following summary statistics: number of invoices and percentage of invoices.
I had trouble using "EXCLUDE" commands to solve this problem, which makes the code difficult to parse without commentary (which neither the solution notebook, nor most of the community provided much of), so I took a different approach.
Because the track ID for each track on each album is unique in the database, the sum of all tracks on an album should be unique for a given album. In reality, two albums end up having the same sum over just the track IDs; however, if you add album ID numbers to these totals, you get a unique sum for each album.
Thus, we can create a dictionary of the unique track/album sum and album ID for each album and match up those two pieces of information with the same measures calculated over invoices. Where the album ID and the unique track/album ID sum match, the customer bought a full album.
Thus, I first created an album + track view (album_track
), then created a dictionary with the album ID and track + album ID sum (album_track_sum_dictionary
).
Next, I joined the invoice line and track tables (invoice_line_invoice_track
) and then created a temporary view with the same measures as the dictionary (invoice_album_track_ID_sums
).
Finally, I marked the unique invoices as including a full album or not based on whether the album ID and unique track & album ID sum matched the dictionary (invoices_marked
), then tallied the absolute number and percentage of invoices where an album was purchased vs. individual tracks.
%%sql
WITH album_track AS
(
SELECT *,
t.name AS track_name
FROM album a
INNER JOIN track t ON t.album_id = a.album_id
),
album_track_sum_dictionary AS
(
SELECT album_id,
title AS album_title,
SUM(track_id) + album_id album_track_sum
FROM album_track
GROUP BY album_id
),
invoice_line_invoice_track AS
(
SELECT *
FROM invoice_line il
INNER JOIN invoice i ON i.invoice_id = il.invoice_id
INNER JOIN track t ON t.track_id = il.track_id
),
invoice_album_track_ID_sums AS
(
SELECT *,
SUM(track_id) + album_id album_track_sum
FROM invoice_line_invoice_track
GROUP BY invoice_id
),
invoices_marked AS
(
SELECT i.*,
CASE
WHEN
(i.album_id = a.album_id)
AND
(i.album_track_sum = a.album_track_sum)
THEN 1
ELSE 0
END AS album_yn
FROM invoice_album_track_ID_sums i
INNER JOIN album_track_sum_dictionary a ON a.album_id = i.album_id
)
SELECT album_yn,
COUNT(*) n_invoices,
ROUND(100.0 * COUNT(album_yn) / SUM(COUNT(*)) OVER(),
2) AS perc_invoices
FROM invoices_marked
GROUP BY album_yn;
* sqlite:///../Datasets/chinook.db Done.
album_yn | n_invoices | perc_invoices |
---|---|---|
0 | 500 | 81.43 |
1 | 114 | 18.57 |
The results show that the Chinook store would be wise not to buy full albums in all cases, as only roughly 19% of invoices are for whole albums. The vast majority of invoices include individually selected tracks.
Whether these are always the same tracks deserves further investigation, as the most common tracks are the ones the store should prioritize. Also, it is possilbe that certain albums tend to be bought more often than individual tracks are bought from them. Determining these further pieces of information would provide better fodder for making a decision on which albums to buy and whether only popular tracks should be bought.