As a capstone to the Intermediate SQL Course, we have been invited to make use of the chinook database to practice our SQL skills and answer some hypothetical business questions.
The first task was getting connected to the chinook database within the jupyter kernel
%%capture
%load_ext sql
%sql sqlite:///chinook.db
'Connected: None@chinook.db'
Next we want to familiarise ourselves with the outputs of the schema within this kernel
Here is the schema:
%%sql
SELECT
name,
type
FROM sqlite_master
WHERE type IN ("table","view");
Done.
name | type |
---|---|
album | table |
artist | table |
customer | table |
employee | table |
genre | table |
invoice | table |
invoice_line | table |
media_type | table |
playlist | table |
playlist_track | table |
track | table |
In this first activity we want to figure out which three of the following four artists to stock in the Chinook record store based on the most popular genres in the USA.
Artist Name | Genre |
---|---|
Regal | Hip-Hop |
Red Tone | Punk |
Meteor and the Girls | Pop |
Slim Jim Bites | Blues |
I always like to see not just the raw numbers but also the content of what is selling, so I pulled a list of all albums and artists with their genres in order of album track sales in the USA.
%%sql
SELECT t.name track_name
,ar.name artist_name
,al.title album_name
,g.name genre
,count(*) tracks_sold
FROM invoice i
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
LEFT JOIN track t ON t.track_id = il.track_id
LEFT JOIN genre g ON g.genre_id = t.genre_id
LEFT JOIN album al ON al.album_id = t.album_id
LEFT JOIN artist ar ON ar.artist_id = al.artist_id
WHERE i.billing_country = 'USA'
GROUP BY track_name, artist_name,album_name,genre
ORDER BY 5 DESC
LIMIT 40
Done.
track_name | artist_name | album_name | genre | tracks_sold |
---|---|---|---|---|
War Pigs | Cake | Cake: B-Sides and Rarities | Alternative | 6 |
You Know I'm No Good (feat. Ghostface Killah) | Amy Winehouse | Back to Black | R&B/Soul | 5 |
End Of The Night | The Doors | The Doors | Rock | 4 |
Evil Woman | Black Sabbath | Black Sabbath | Metal | 4 |
Highway Chile | Jimi Hendrix | Are You Experienced? | Rock | 4 |
I Looked At You | The Doors | The Doors | Rock | 4 |
Night Of The Long Knives | AC/DC | For Those About To Rock We Salute You | Rock | 4 |
Scentless Apprentice | Nirvana | From The Muddy Banks Of The Wishkah [live] | Rock | 4 |
Violent Pornography | System Of A Down | Mezmerize | Metal | 4 |
Are You Experienced? | Jimi Hendrix | Are You Experienced? | Rock | 3 |
Back Door Man | The Doors | The Doors | Rock | 3 |
Behind The Wall Of Sleep | Black Sabbath | Black Sabbath | Metal | 3 |
Big City Nights | Scorpions | 20th Century Masters - The Millennium Collection: The Best of Scorpions | Rock | 3 |
Dog Eat Dog | AC/DC | Let There Be Rock | Rock | 3 |
Estranged | Guns N' Roses | Use Your Illusion II | Metal | 3 |
Hairshirt | R.E.M. | Green | Alternative & Punk | 3 |
Heart-Shaped Box | Nirvana | From The Muddy Banks Of The Wishkah [live] | Rock | 3 |
Hey Joe | Jimi Hendrix | Are You Experienced? | Rock | 3 |
I Can't Remember | Alice In Chains | Facelift | Rock | 3 |
I Stand Alone | Godsmack | Faceless | Metal | 3 |
Light My Fire | The Doors | The Doors | Rock | 3 |
Love, Hate, Love | Alice In Chains | Facelift | Rock | 3 |
Midnight | Red Hot Chili Peppers | By The Way | Rock | 3 |
Milk It | Nirvana | From The Muddy Banks Of The Wishkah [live] | Rock | 3 |
No One Like You | Scorpions | 20th Century Masters - The Millennium Collection: The Best of Scorpions | Rock | 3 |
Orange Crush | R.E.M. | Green | Alternative & Punk | 3 |
Polly | Nirvana | Nevermind | Rock | 3 |
Radio/Video | System Of A Down | Mezmerize | Metal | 3 |
Re-Align | Godsmack | Faceless | Metal | 3 |
Revenga | System Of A Down | Mezmerize | Metal | 3 |
Sleeping Village | Black Sabbath | Black Sabbath | Metal | 3 |
Soldier Side - Intro | System Of A Down | Mezmerize | Metal | 3 |
Spank Thru | Nirvana | From The Muddy Banks Of The Wishkah [live] | Rock | 3 |
Straight Out Of Line | Godsmack | Faceless | Metal | 3 |
Third Stone From The Sun | Jimi Hendrix | Are You Experienced? | Rock | 3 |
Time Is On My Side | The Rolling Stones | Hot Rocks, 1964-1971 (Disc 1) | Rock | 3 |
Twentienth Century Fox | The Doors | The Doors | Rock | 3 |
We Die Young | Alice In Chains | Facelift | Rock | 3 |
You Are The Everything | R.E.M. | Green | Alternative & Punk | 3 |
You're What's Happening (In The World Today) | Marvin Gaye | Seek And Shall Find: More Of The Best (1963-1981) | R&B/Soul | 3 |
Next I pulled the total number of tracks sold in the USA
%%sql
SELECT count(*) tracks_sold
FROM invoice i
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
WHERE i.billing_country = 'USA'
Done.
tracks_sold |
---|
1051 |
I subsequently calculated the total number of tracks sold in each genre and the percentage of total sales for each.
%%sql
SELECT g.name genre
,count(invoice_line_id) tracks_sold
,ROUND(count(*)*100.0/(SELECT count(*)
FROM invoice i
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
WHERE i.billing_country = 'USA'),2) genre_sales_pct
FROM invoice i
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
LEFT JOIN track t ON t.track_id = il.track_id
LEFT JOIN genre g ON g.genre_id = t.genre_id
WHERE i.billing_country = 'USA'
GROUP BY genre
ORDER BY 2 DESC
Done.
genre | tracks_sold | genre_sales_pct |
---|---|---|
Rock | 561 | 53.38 |
Alternative & Punk | 130 | 12.37 |
Metal | 124 | 11.8 |
R&B/Soul | 53 | 5.04 |
Blues | 36 | 3.43 |
Alternative | 35 | 3.33 |
Latin | 22 | 2.09 |
Pop | 22 | 2.09 |
Hip Hop/Rap | 20 | 1.9 |
Jazz | 14 | 1.33 |
Easy Listening | 13 | 1.24 |
Reggae | 6 | 0.57 |
Electronica/Dance | 5 | 0.48 |
Classical | 4 | 0.38 |
Heavy Metal | 3 | 0.29 |
Soundtrack | 2 | 0.19 |
TV Shows | 1 | 0.1 |
Whilst Rock is the top genre in the USA, Punk, Pop & Blues are the best choices based on their relative popularity compare to Hip-Hop.
Therefore the Chinook record store should stock:
Artist Name | Genre |
---|---|
Red Tone | Punk |
Meteor and the Girls | Pop |
Slim Jim Bites | Blues |
In this next activity we want to ascertain the factors influencing the performance of the sales support staff at Chinook.
First off I generated a table of all relevant data for employees and their customers purchases.
%%sql
SELECT sr.*,i.*,il.*,ar.*
FROM invoice i
LEFT JOIN customer c ON i.customer_id = c.customer_id
LEFT JOIN employee sr ON c.support_rep_id = sr.employee_id
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
LEFT JOIN track t ON t.track_id = il.track_id
LEFT JOIN album al ON al.album_id = t.album_id
LEFT JOIN artist ar ON ar.artist_id = al.artist_id
LIMIT 50
Done.
employee_id | last_name | first_name | title | reports_to | birthdate | hire_date | address | city | state | country | postal_code | phone | fax | invoice_id | customer_id | invoice_date | billing_address | billing_city | billing_state | billing_country | billing_postal_code | total | invoice_line_id | invoice_id_1 | track_id | unit_price | quantity | artist_id | 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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 1 | 1 | 1158 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 2 | 1 | 1159 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 3 | 1 | 1160 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 4 | 1 | 1161 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 5 | 1 | 1162 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 6 | 1 | 1163 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 7 | 1 | 1164 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 8 | 1 | 1165 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 9 | 1 | 1166 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 10 | 1 | 1167 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 11 | 1 | 1168 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 12 | 1 | 1169 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 13 | 1 | 1170 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 14 | 1 | 1171 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 15 | 1 | 1172 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 1 | 18 | 2017-01-03 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 15.84 | 16 | 1 | 1173 | 0.99 | 1 | 88 | Guns N' Roses |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 17 | 2 | 3476 | 0.99 | 1 | 252 | Amy Winehouse |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 18 | 2 | 482 | 0.99 | 1 | 54 | Green Day |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 19 | 2 | 2701 | 0.99 | 1 | 142 | The Rolling Stones |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 20 | 2 | 1641 | 0.99 | 1 | 22 | Led Zeppelin |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 21 | 2 | 1119 | 0.99 | 1 | 81 | Eric Clapton |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 22 | 2 | 2324 | 0.99 | 1 | 124 | R.E.M. |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 23 | 2 | 201 | 0.99 | 1 | 15 | Buddy Guy |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 24 | 2 | 819 | 0.99 | 1 | 58 | Deep Purple |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 25 | 2 | 392 | 0.99 | 1 | 6 | Antônio Carlos Jobim |
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 | 2 | 30 | 2017-01-03 00:00:00 | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | 9.9 | 26 | 2 | 2651 | 0.99 | 1 | 141 | The Police |
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 | 3 | 40 | 2017-01-05 00:00:00 | 8, Rue Hanovre | Paris | None | France | 75002 | 1.98 | 27 | 3 | 2516 | 0.99 | 1 | 132 | Soundgarden |
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 | 3 | 40 | 2017-01-05 00:00:00 | 8, Rue Hanovre | Paris | None | France | 75002 | 1.98 | 28 | 3 | 2646 | 0.99 | 1 | 140 | The Doors |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 29 | 4 | 3448 | 0.99 | 1 | 247 | The King's Singers |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 30 | 4 | 2560 | 0.99 | 1 | 135 | System Of A Down |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 31 | 4 | 3336 | 0.99 | 1 | 196 | Cake |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 32 | 4 | 829 | 0.99 | 1 | 78 | Def Leppard |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 33 | 4 | 1872 | 0.99 | 1 | 50 | Metallica |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 34 | 4 | 748 | 0.99 | 1 | 58 | Deep Purple |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 35 | 4 | 1778 | 0.99 | 1 | 104 | Marvin Gaye |
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 | 4 | 18 | 2017-01-06 00:00:00 | 627 Broadway | New York | NY | USA | 10012-2612 | 7.92 | 36 | 4 | 2514 | 0.99 | 1 | 132 | Soundgarden |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 37 | 5 | 1986 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 38 | 5 | 1987 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 39 | 5 | 1988 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 40 | 5 | 1989 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 41 | 5 | 1990 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 42 | 5 | 1991 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 43 | 5 | 1992 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 44 | 5 | 1993 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 45 | 5 | 1994 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 46 | 5 | 1995 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 47 | 5 | 1996 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 48 | 5 | 1997 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 49 | 5 | 1998 | 0.99 | 1 | 110 | Nirvana |
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 | 5 | 27 | 2017-01-07 00:00:00 | 1033 N Park Ave | Tucson | AZ | USA | 85719 | 16.83 | 50 | 5 | 1999 | 0.99 | 1 | 110 | Nirvana |
I then generated a summary of the following by each employee (bold = required)
%%sql
WITH revenue_base AS (
SELECT sr.*,i.*
FROM invoice i
LEFT JOIN customer c ON i.customer_id = c.customer_id
LEFT JOIN employee sr ON c.support_rep_id = sr.employee_id
)
, detail_base AS(SELECT sr.*,i.*,il.*,ar.*
FROM invoice i
LEFT JOIN customer c ON i.customer_id = c.customer_id
LEFT JOIN employee sr ON c.support_rep_id = sr.employee_id
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
LEFT JOIN track t ON t.track_id = il.track_id
LEFT JOIN album al ON al.album_id = t.album_id
LEFT JOIN artist ar ON ar.artist_id = al.artist_id
)
SELECT
b.first_name||" "||b.last_name support_rep_name
,SUM(b.total) total_revenue
,ROUND(SUM(b.total)*100.0/(SELECT sum(b2.total)
FROM revenue_base b2),2) revenue_pct
,COUNT(b.invoice_id) no_purchases
,COUNT(DISTINCT b.customer_id) no_of_clients
,(SELECT COUNT(DISTINCT b2.invoice_line_id)/COUNT(DISTINCT b2.invoice_id)
FROM detail_base b2
WHERE b.employee_id=b2.employee_id) avg_tracks_per_sale
,ROUND(SUM(b.total)/COUNT(b.invoice_id),2) avg_revenue_pp
,(SELECT b2.name artist
FROM detail_base b2
WHERE b.employee_id = b2.employee_id
GROUP BY artist
ORDER BY MAX(unit_price) DESC
,COUNT(invoice_id) DESC
,MAX(total) DESC
LIMIT 1) top_selling_artist
,MIN (b.invoice_date) first_sale
FROM revenue_base b
GROUP BY employee_id,support_rep_name
ORDER BY 2 DESC, 4 DESC;
Done.
support_rep_name | total_revenue | revenue_pct | no_purchases | no_of_clients | avg_tracks_per_sale | avg_revenue_pp | top_selling_artist | first_sale |
---|---|---|---|---|---|---|---|---|
Jane Peacock | 1731.5100000000039 | 36.77 | 212 | 21 | 8 | 8.17 | Queen | 2017-01-03 00:00:00 |
Margaret Park | 1584.0000000000034 | 33.63 | 214 | 20 | 7 | 7.4 | Foo Fighters | 2017-01-05 00:00:00 |
Steve Johnson | 1393.920000000002 | 29.6 | 188 | 18 | 7 | 7.41 | Jimi Hendrix | 2017-01-10 00:00:00 |
To add context to the top selling artist, I also looked at the sales rate of these artists and the maximum prices of their music
I found overall that most artists music sells for ~99c, especially the most popular songs
%%sql
SELECT DISTINCT
ar.name artist
,MAX(il.unit_price) max_track_price
,count(il.invoice_line_id) no_tracks_sold
FROM artist ar
LEFT JOIN album al ON al.artist_id = ar.artist_id
LEFT JOIN track t ON t.album_id = al.album_id
LEFT JOIN invoice_line il ON il.track_id = t.track_id
GROUP BY artist
ORDER BY 3 DESC
LIMIT 30
Done.
artist | max_track_price | no_tracks_sold |
---|---|---|
Queen | 0.99 | 192 |
Jimi Hendrix | 0.99 | 187 |
Nirvana | 0.99 | 130 |
Red Hot Chili Peppers | 0.99 | 130 |
Pearl Jam | 0.99 | 129 |
AC/DC | 0.99 | 124 |
Guns N' Roses | 0.99 | 124 |
Foo Fighters | 0.99 | 121 |
The Rolling Stones | 0.99 | 117 |
Metallica | 0.99 | 106 |
Green Day | 0.99 | 105 |
R.E.M. | 0.99 | 104 |
Amy Winehouse | 0.99 | 97 |
Godsmack | 0.99 | 96 |
Eric Clapton | 0.99 | 94 |
System Of A Down | 0.99 | 93 |
Van Halen | 0.99 | 93 |
JET | 0.99 | 90 |
U2 | 0.99 | 89 |
Iron Maiden | 0.99 | 84 |
The Doors | 0.99 | 83 |
Aerosmith | 0.99 | 80 |
The Police | 0.99 | 80 |
The Who | 0.99 | 76 |
Alanis Morissette | 0.99 | 75 |
Frank Sinatra | 0.99 | 74 |
Miles Davis | 0.99 | 74 |
Scorpions | 0.99 | 74 |
The Clash | 0.99 | 72 |
Led Zeppelin | 0.99 | 71 |
Overall, it seems their revenue split is proportional to the number of clients they have which seems to be a function of their tenure at the company
For this activity, we want to calculate data, for each country, on the:
Grouping smaller markets as 'Other' and forcing that row to the bottom of the results
%%sql
WITH country_clients AS(
SELECT c.country client_country
,COUNT(DISTINCT c.customer_id) no_of_customers
,SUM(i.total) total_revenue
,AVG(i.total) avg_order_value
FROM customer c
LEFT JOIN invoice i ON i. customer_id = c.customer_id
GROUP BY client_country)
, summary AS(
SELECT
(CASE WHEN no_of_customers = 1 THEN 'Other'
ELSE client_country END) country
,SUM(no_of_customers) no_of_customers
,ROUND(SUM(total_revenue),2) total_revenue
,ROUND(SUM(total_revenue)/SUM(no_of_customers),2) revenue_per_client
,ROUND(AVG(avg_order_value),2) avg_order_value
,(CASE WHEN avg(no_of_customers) = 1 THEN 2
ELSE 1 END) ranking
FROM country_clients
GROUP BY 1)
SELECT country, no_of_customers, total_revenue, revenue_per_client,avg_order_value
FROM summary
ORDER BY ranking ASC,3 DESC, 4 DESC;
Done.
country | no_of_customers | total_revenue | revenue_per_client | avg_order_value |
---|---|---|---|---|
USA | 13 | 1040.49 | 80.04 | 7.94 |
Canada | 8 | 535.59 | 66.95 | 7.05 |
Brazil | 5 | 427.68 | 85.54 | 7.01 |
France | 5 | 389.07 | 77.81 | 7.78 |
Germany | 4 | 334.62 | 83.65 | 8.16 |
Czech Republic | 2 | 273.24 | 136.62 | 9.11 |
United Kingdom | 3 | 245.52 | 81.84 | 8.77 |
Portugal | 2 | 185.13 | 92.56 | 6.38 |
India | 2 | 183.15 | 91.57 | 8.72 |
Other | 15 | 1094.94 | 73.0 | 7.45 |
I opted to arrange this query using CTEs for clarity.
country_clients
gave the basic summmary of all countriessummary
employs two CASE clauses, one to identify the small markets, and the other to label them for to be listed lastsummary
CTE with the exception of the ranking
column which is just used as the first ORDER BY conditionThis activity requires identification of orders as either comprising whole albums or collections made up of many individual tracks (disregarding purchases of whole albums made by selecting all tracks)
This is to drive the decision as to whether the Chinook store should move away from offering sales of full albums and only purchase and sell the most popular tracks for the sake of saving costs
I found this really challenging, but eventually I arrived at the solution in my own style using the following:
To use the EXCEPT function accurately:
%%sql
WITH base AS (
SELECT i.invoice_id
,al.album_id
FROM invoice i
LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
LEFT JOIN track t ON il.track_id = t.track_id
LEFT JOIN album al ON t.album_id =al.album_id
)
, album_tracks AS (
SELECT al.album_id
,t.track_id
FROM track t
LEFT JOIN album al
ON t.album_id =al.album_id)
, purchase_types AS(
SELECT base.invoice_id
,(CASE WHEN (SELECT t.track_id
FROM track t
WHERE album_id = base.album_id
EXCEPT
SELECT il.track_id
FROM invoice_line il
WHERE il.invoice_id = base.invoice_id) IS NULL
AND
(SELECT il.track_id
FROM invoice_line il
WHERE il.invoice_id = base.invoice_id
EXCEPT
SELECT t.track_id
FROM track t
WHERE album_id = base.album_id) IS NULL
THEN 'Album'
ELSE 'Collection' END) purchase_type
FROM base
)
SELECT
purchase_type
,COUNT(DISTINCT invoice_id) AS no_invoices
,ROUND(COUNT(DISTINCT invoice_id)*100
/(SELECT COUNT(DISTINCT invoice_id)
FROM base),2) AS percentage
FROM purchase_types
GROUP BY purchase_type
UNION
SELECT
'Total'
,COUNT(DISTINCT invoice_id)
,ROUND(COUNT(DISTINCT invoice_id)*100/(SELECT COUNT(DISTINCT invoice_id)
FROM base),2)
FROM base
Done.
purchase_type | no_invoices | percentage |
---|---|---|
Album | 114 | 18.0 |
Collection | 500 | 81.0 |
Total | 614 | 100.0 |
The majority of customers purchase custom collections of songs, however their is a non-negligable minority (almost 20%) of customers who do still bu complete albums. It would potentially be unwise to limit these clients' freedom to purchase how they like for the sake of margins as we might lose custom along the way which would be counter intuitive.
Further analysis into the types of albums purchases and perhaps listening data if we can gather it could help make a more nuanced intervention if we still want to pursue this line of thought.
The answer to this is interesting, from its wording I started off by looking at the most playlists featuring each artist. Here, we find there are lots of artists featured lightly on many playlists. But then, looking at the number of times an artist is featured on playlist, this is more intersting. We find more usefully which artists are featured heavily on but on fewer playlists.
%%sql
WITH base AS (
SELECT pl.*
,ar.*
FROM playlist pl
LEFT JOIN playlist_track plt ON plt.playlist_id = pl.playlist_id
LEFT JOIN track t ON t.track_id = plt.track_id
LEFT JOIN album al ON al.album_id = t.album_id
LEFT JOIN artist ar ON ar.artist_id = al.artist_id)
SELECT DISTINCT "name:1" artist
,COUNT(DISTINCT playlist_id) no_playlists
,COUNT(playlist_id) total_features
FROM base
GROUP BY artist
ORDER BY 3 DESC, 2 DESC
Done.
artist | no_playlists | total_features |
---|---|---|
Iron Maiden | 4 | 516 |
U2 | 3 | 333 |
Metallica | 4 | 296 |
Led Zeppelin | 3 | 252 |
Deep Purple | 3 | 226 |
Lost | 2 | 184 |
Pearl Jam | 4 | 177 |
Eric Clapton | 4 | 145 |
Faith No More | 3 | 145 |
Lenny Kravitz | 3 | 143 |
Red Hot Chili Peppers | 3 | 128 |
Van Halen | 3 | 122 |
Os Paralamas Do Sucesso | 3 | 119 |
Guns N' Roses | 3 | 114 |
Titãs | 3 | 114 |
Various Artists | 2 | 112 |
The Rolling Stones | 3 | 111 |
Chico Science & Nação Zumbi | 3 | 108 |
The Office | 2 | 106 |
Kiss | 3 | 104 |
Foo Fighters | 3 | 101 |
Jamiroquai | 3 | 96 |
R.E.M. | 3 | 96 |
Queen | 3 | 94 |
Nirvana | 4 | 93 |
Legião Urbana | 3 | 93 |
Chico Buarque | 4 | 87 |
Smashing Pumpkins | 3 | 85 |
Djavan | 4 | 83 |
Green Day | 3 | 83 |
Gilberto Gil | 4 | 82 |
Audioslave | 3 | 81 |
Creedence Clearwater Revival | 2 | 80 |
Ozzy Osbourne | 4 | 79 |
Cidade Negra | 3 | 76 |
The Tea Party | 3 | 76 |
Miles Davis | 3 | 75 |
Santana | 3 | 75 |
Tim Maia | 3 | 67 |
Antônio Carlos Jobim | 3 | 64 |
The Cult | 3 | 64 |
Caetano Veloso | 4 | 63 |
Cássia Eller | 3 | 62 |
James Brown | 3 | 60 |
House Of Pain | 3 | 57 |
Skank | 3 | 57 |
Zeca Pagodinho | 3 | 57 |
Lulu Santos | 2 | 56 |
Milton Nascimento | 3 | 55 |
Spyro Gyra | 3 | 54 |
The Clash | 3 | 54 |
Mötley Crüe | 4 | 52 |
Body Count | 3 | 51 |
Soundgarden | 4 | 49 |
Def Leppard | 3 | 48 |
Funk Como Le Gusta | 3 | 48 |
Planet Hemp | 3 | 48 |
Battlestar Galactica (Classic) | 2 | 48 |
Frank Sinatra | 2 | 48 |
Amy Winehouse | 2 | 46 |
Heroes | 2 | 46 |
Aerosmith | 3 | 45 |
Gonzaguinha | 4 | 44 |
Gene Krupa | 2 | 44 |
Ed Motta | 3 | 42 |
Passengers | 3 | 42 |
Rush | 3 | 42 |
The Police | 3 | 42 |
UB40 | 3 | 42 |
Battlestar Galactica | 2 | 40 |
The Who | 2 | 40 |
Alanis Morissette | 3 | 39 |
Incognito | 3 | 39 |
The Black Crowes | 2 | 38 |
Alice In Chains | 4 | 37 |
Stone Temple Pilots | 4 | 37 |
AC/DC | 3 | 37 |
BackBeat | 3 | 36 |
Black Sabbath | 3 | 36 |
Jota Quest | 3 | 36 |
Page & Plant | 3 | 36 |
Black Label Society | 2 | 36 |
Marisa Monte | 2 | 36 |
Marvin Gaye | 2 | 36 |
Jimi Hendrix | 2 | 34 |
Marcos Valle | 2 | 34 |
O Rappa | 2 | 34 |
Bruce Dickinson | 3 | 33 |
Falamansa | 3 | 33 |
Judas Priest | 3 | 33 |
R.E.M. Feat. Kate Pearson | 3 | 33 |
Motörhead | 3 | 32 |
Temple of the Dog | 4 | 31 |
Elis Regina | 3 | 31 |
Jorge Ben | 3 | 31 |
Joe Satriani | 3 | 30 |
Raimundos | 3 | 30 |
O Terço | 2 | 30 |
Toquinho & Vinícius | 2 | 30 |
Vinícius De Moraes | 2 | 30 |
Chris Cornell | 2 | 28 |
João Suplicy | 2 | 28 |
Olodum | 2 | 28 |
Os Mutantes | 2 | 28 |
Raul Seixas | 2 | 28 |
JET | 2 | 26 |
Velvet Revolver | 2 | 26 |
Scorpions | 3 | 25 |
Apocalyptica | 3 | 24 |
David Coverdale | 2 | 24 |
Godsmack | 2 | 24 |
Mônica Marianno | 2 | 24 |
Buddy Guy | 2 | 22 |
System Of A Down | 2 | 22 |
The Doors | 2 | 22 |
Terry Bozzio, Tony Levin & Steve Stevens | 3 | 21 |
Cláudio Zoli | 2 | 20 |
Marillion | 2 | 20 |
Men At Work | 2 | 20 |
Paul D'Ianno | 2 | 20 |
Stevie Ray Vaughan & Double Trouble | 2 | 20 |
Dennis Chambers | 2 | 18 |
Frank Zappa & Captain Beefheart | 2 | 18 |
Pink Floyd | 2 | 18 |
Billy Cobham | 2 | 16 |
Eugene Ormandy | 7 | 15 |
Accept | 4 | 15 |
Berliner Philharmoniker & Herbert Von Karajan | 6 | 13 |
Academy of St. Martin in the Fields & Sir Neville Marriner | 6 | 10 |
The King's Singers | 6 | 10 |
Michael Tilson Thomas & San Francisco Symphony | 5 | 10 |
English Concert & Trevor Pinnock | 6 | 9 |
Habib Koité and Bamada | 3 | 6 |
Academy of St. Martin in the Fields, Sir Neville Marriner & Thurston Dart | 5 | 5 |
Adrian Leaper & Doreen de Feis | 5 | 5 |
Alberto Turco & Nova Schola Gregoriana | 5 | 5 |
Antal Doráti & London Symphony Orchestra | 5 | 5 |
Barry Wordsworth & BBC Concert Orchestra | 5 | 5 |
Berliner Philharmoniker & Hans Rosbaud | 5 | 5 |
Boston Symphony Orchestra & Seiji Ozawa | 5 | 5 |
Britten Sinfonia, Ivor Bolton & Lesley Garrett | 5 | 5 |
Chicago Symphony Chorus, Chicago Symphony Orchestra & Sir Georg Solti | 5 | 5 |
Chicago Symphony Orchestra & Fritz Reiner | 5 | 5 |
Chor der Wiener Staatsoper, Herbert Von Karajan & Wiener Philharmoniker | 5 | 5 |
Emanuel Ax, Eugene Ormandy & Philadelphia Orchestra | 5 | 5 |
Gustav Mahler | 5 | 5 |
James Levine | 5 | 5 |
Julian Bream | 5 | 5 |
Kent Nagano and Orchestre de l'Opéra de Lyon | 5 | 5 |
Luciano Pavarotti | 5 | 5 |
Martin Roscoe | 5 | 5 |
Maurizio Pollini | 5 | 5 |
Mela Tenenbaum, Pro Musica Prague & Richard Kapp | 5 | 5 |
Orchestre Révolutionnaire et Romantique & John Eliot Gardiner | 5 | 5 |
Philharmonia Orchestra & Sir Neville Marriner | 5 | 5 |
Philip Glass Ensemble | 5 | 5 |
Richard Marlow & The Choir of Trinity College, Cambridge | 5 | 5 |
Scholars Baroque Ensemble | 5 | 5 |
Sir Georg Solti & Wiener Philharmoniker | 5 | 5 |
Sir Georg Solti, Sumi Jo & Wiener Philharmoniker | 5 | 5 |
Ton Koopman | 5 | 5 |
Wilhelm Kempff | 5 | 5 |
Yo-Yo Ma | 5 | 5 |
None | 4 | 4 |
Aaron Copland & London Symphony Orchestra | 4 | 4 |
Academy of St. Martin in the Fields Chamber Ensemble & Sir Neville Marriner | 4 | 4 |
Academy of St. Martin in the Fields, John Birch, Sir Neville Marriner & Sylvia McNair | 4 | 4 |
Anne-Sophie Mutter, Herbert Von Karajan & Wiener Philharmoniker | 4 | 4 |
Berliner Philharmoniker, Claudio Abbado & Sabine Meyer | 4 | 4 |
C. Monteverdi, Nigel Rogers - Chiaroscuro; London Baroque; London Cornett & Sackbu | 4 | 4 |
Charles Dutoit & L'Orchestre Symphonique de Montréal | 4 | 4 |
Choir Of Westminster Abbey & Simon Preston | 4 | 4 |
Christopher O'Riley | 4 | 4 |
Edo de Waart & San Francisco Symphony | 4 | 4 |
Emerson String Quartet | 4 | 4 |
Equale Brass Ensemble, John Eliot Gardiner & Munich Monteverdi Orchestra and Choir | 4 | 4 |
Felix Schmidt, London Symphony Orchestra & Rafael Frühbeck de Burgos | 4 | 4 |
Fretwork | 4 | 4 |
Gerald Moore | 4 | 4 |
Göteborgs Symfoniker & Neeme Järvi | 4 | 4 |
Herbert Von Karajan, Mirella Freni & Wiener Philharmoniker | 4 | 4 |
Hilary Hahn, Jeffrey Kahane, Los Angeles Chamber Orchestra & Margaret Batjer | 4 | 4 |
Itzhak Perlman | 4 | 4 |
Leonard Bernstein & New York Philharmonic | 4 | 4 |
Les Arts Florissants & William Christie | 4 | 4 |
London Symphony Orchestra & Sir Charles Mackerras | 4 | 4 |
Michele Campanella | 4 | 4 |
Nash Ensemble | 4 | 4 |
Orchestra of The Age of Enlightenment | 4 | 4 |
Otto Klemperer & Philharmonia Orchestra | 4 | 4 |
Roger Norrington, London Classical Players | 4 | 4 |
Royal Philharmonic Orchestra & Sir Thomas Beecham | 4 | 4 |
Sergei Prokofiev & Yuri Temirkanov | 4 | 4 |
The 12 Cellists of The Berlin Philharmonic | 4 | 4 |
Yehudi Menuhin | 4 | 4 |
Aisha Duo | 2 | 4 |
Karsh Kale | 2 | 4 |
The Posies | 2 | 4 |
Dread Zeppelin | 3 | 3 |
Nicolaus Esterhazy Sinfonia | 3 | 3 |
Aaron Goldberg | 2 | 2 |
Aquaman | 2 | 2 |
Cake | 2 | 2 |
Calexico | 2 | 2 |
Luciana Souza/Romero Lubambo | 2 | 2 |
Almost half of the songs on the database have never been purchased! That's a lot!
%%sql
SELECT ever_purchased
, COUNT (track_id) no_tracks
FROM(
SELECT t.track_id
,SUM(il.quantity) total_purchases
,CASE WHEN SUM(il.quantity) >0 THEN 'Yes'
ELSE 'No' END ever_purchased
FROM track t
LEFT JOIN invoice_line il ON il.track_id = t.track_id
GROUP BY t.track_id)
GROUP BY ever_purchased
Done.
ever_purchased | no_tracks |
---|---|
No | 1697 |
Yes | 1806 |
Arguably not, the most popular genres are also the ones with the most supply, but if we look at avg sales per track, the top three genres are
Genre |
---|
Easy Listening |
Alternative |
R&B/Soul |
So I would recommend that we stock more of these.
%%sql
SELECT g.name genre
,COUNT(invoice_line_id) tracks_sold
,COUNT(DISTINCT t.track_id) no_available
,ROUND(COUNT(invoice_line_id)*1.0/COUNT(DISTINCT t.track_id),2) avg_sales_per_track
,ROUND(count(invoice_line_id)*100.0/(SELECT count(invoice_line_id)
FROM invoice i
LEFT JOIN invoice_line il
ON i.invoice_id = il.invoice_id),2) genre_sales_pct
FROM track t
LEFT JOIN invoice_line il ON t.track_id = il.track_id
LEFT JOIN invoice i ON i.invoice_id = il.invoice_id
LEFT JOIN genre g ON g.genre_id = t.genre_id
GROUP BY genre
ORDER BY 4 DESC,5 DESC
Done.
genre | tracks_sold | no_available | avg_sales_per_track | genre_sales_pct |
---|---|---|---|---|
Easy Listening | 74 | 24 | 3.08 | 1.56 |
Alternative | 117 | 40 | 2.92 | 2.46 |
R&B/Soul | 159 | 61 | 2.61 | 3.34 |
Rock | 2635 | 1297 | 2.03 | 55.39 |
Electronica/Dance | 55 | 30 | 1.83 | 1.16 |
Metal | 619 | 374 | 1.66 | 13.01 |
Blues | 124 | 81 | 1.53 | 2.61 |
Alternative & Punk | 492 | 332 | 1.48 | 10.34 |
Pop | 63 | 48 | 1.31 | 1.32 |
Hip Hop/Rap | 33 | 35 | 0.94 | 0.69 |
Jazz | 121 | 130 | 0.93 | 2.54 |
Classical | 47 | 74 | 0.64 | 0.99 |
Reggae | 35 | 58 | 0.6 | 0.74 |
Latin | 167 | 579 | 0.29 | 3.51 |
Heavy Metal | 8 | 28 | 0.29 | 0.17 |
Soundtrack | 5 | 43 | 0.12 | 0.11 |
TV Shows | 2 | 93 | 0.02 | 0.04 |
Drama | 1 | 64 | 0.02 | 0.02 |
Bossa Nova | 0 | 15 | 0.0 | 0.0 |
Comedy | 0 | 17 | 0.0 | 0.0 |
Opera | 0 | 1 | 0.0 | 0.0 |
Rock And Roll | 0 | 12 | 0.0 | 0.0 |
Sci Fi & Fantasy | 0 | 26 | 0.0 | 0.0 |
Science Fiction | 0 | 13 | 0.0 | 0.0 |
World | 0 | 28 | 0.0 | 0.0 |
%%sql
SELECT CASE WHEN mt.name LIKE 'Protected%' THEN 'Protected'
ELSE 'Non_Protected' END media_type
,COUNT(invoice_line_id) tracks_sold
,COUNT(DISTINCT t.track_id) no_available
,ROUND(COUNT(invoice_line_id)*1.0/COUNT(DISTINCT t.track_id),2) avg_sales_per_track
,ROUND(count(invoice_line_id)*100.0/(SELECT count(invoice_line_id)
FROM invoice i
LEFT JOIN invoice_line il
ON i.invoice_id = il.invoice_id),2) genre_sales_pct
FROM track t
LEFT JOIN invoice_line il ON t.track_id = il.track_id
LEFT JOIN invoice i ON i.invoice_id = il.invoice_id
LEFT JOIN media_type mt ON mt.media_type_id = t.media_type_id
GROUP BY media_type
ORDER BY 4 DESC,5 DESC
Done.
media_type | tracks_sold | no_available | avg_sales_per_track | genre_sales_pct |
---|---|---|---|---|
Non_Protected | 4315 | 3052 | 1.41 | 90.71 |
Protected | 442 | 451 | 0.98 | 9.29 |
In short, yes. Non-Protected tracks sell 9x as many as Protected tracks