Guided Project: Answering Business Questions Using SQL

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.

Setting Up

The first task was getting connected to the chinook database within the jupyter kernel

In [1]:
%%capture
%load_ext sql
%sql sqlite:///chinook.db
Out[1]:
'Connected: None@chinook.db'

Testing Functionalities

Next we want to familiarise ourselves with the outputs of the schema within this kernel

Here is the schema: chinook database schema

In [2]:
%%sql
SELECT
    name,
    type
FROM sqlite_master
WHERE type IN ("table","view");
Done.
Out[2]:
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

Familiarisation & Context

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.

In [3]:
%%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.
Out[3]:
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

In [4]:
%%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.
Out[4]:
tracks_sold
1051

I subsequently calculated the total number of tracks sold in each genre and the percentage of total sales for each.

In [5]:
%%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.
Out[5]:
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

Task One Conclusion

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

Task Two: Chinook Employee Evaluation

In this next activity we want to ascertain the factors influencing the performance of the sales support staff at Chinook.

Familiarisation & Context

First off I generated a table of all relevant data for employees and their customers purchases.

In [6]:
%%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.
Out[6]:
employee_id last_name first_name title reports_to birthdate hire_date address city state country postal_code phone fax email 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

Analysis

I then generated a summary of the following by each employee (bold = required)

  • total revenue from sales
  • total sales orders
  • total number of clients
  • average tracks sold per order
  • average revenue per order
  • top selling artist
  • date of first sale by each employee
In [7]:
%%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.
Out[7]:
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

Additional Context

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

In [8]:
%%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.
Out[8]:
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

Summary of Findings

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

  • Jane is the rep with the highest revenue, despite Margaret generating more sales.
  • Interestingly , Jane is also selling on average one more track in each sale than her peers which puts her share of the revenue generated at 3.3% above the average. Margaret generates 4% more of the revenues than Steve, with the lowest.
  • As the newest member of the team, Steve is generating lower revenue due to simply making fewer sales, as his tracks per sale and revenue per sale is equal to Margaret

Task Three: Sales Performance by Country

For this activity, we want to calculate data, for each country, on the:

  • total number of customers
  • total value of sales
  • average value of sales per customer
  • average order value

Grouping smaller markets as 'Other' and forcing that row to the bottom of the results

In [9]:
%%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.
Out[9]:
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

Notes:

I opted to arrange this query using CTEs for clarity.

  • The first CTE country_clients gave the basic summmary of all countries
  • The second CTE summary employs two CASE clauses, one to identify the small markets, and the other to label them for to be listed last
  • The final query selects all outputs of the summary CTE with the exception of the ranking column which is just used as the first ORDER BY condition

Task Four: Album vs Collection Purchases

This 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

Working Out Steps

I found this really challenging, but eventually I arrived at the solution in my own style using the following:

  1. a base CTE with all the albums associated with each invoice
  2. an intermidate CTE with all the tracks on each album
  3. a secondary intermediate CTE to label the invoices

Notes on the subqueries embedded in the CASE WHEN clauses

To use the EXCEPT function accurately:

  • First write out the query to SELECT the following:
    • a) All track_ids of tracks which are on a given invoice_id
    • b) All track_ids of tracks which are on a given album
  • Then ensure your references tie back relevantly to your intermediate CTEs
In [10]:
%%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.
Out[10]:
purchase_type no_invoices percentage
Album 114 18.0
Collection 500 81.0
Total 614 100.0

Task Four: Conclusion

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.

Extension Activities

Activity One: Which artist is used in the most playlists?

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.

In [11]:
%%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.
Out[11]:
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

Activity Two: How many tracks have been purchased vs not purchased?

Almost half of the songs on the database have never been purchased! That's a lot!

In [12]:
%%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.
Out[12]:
ever_purchased no_tracks
No 1697
Yes 1806

Activity Three: Is the range of tracks in the store reflective of their sales popularity?

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.

In [13]:
%%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.
Out[13]:
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

Activity Four: Do protected vs non-protected media types have an effect on popularity?

In [14]:
%%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.
Out[14]:
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

Summary

In short, yes. Non-Protected tracks sell 9x as many as Protected tracks