SQL Business Analysis Project

This project is part of my DataQuest course and is one of the projects in the SQL module. This project is about the business data for a music download company.

#install.packages("RSQLite")

Warning messages:
1: In readChar(file, size, TRUE) : truncating string with embedded nuls
2: In readChar(file, size, TRUE) : truncating string with embedded nuls
3: In readChar(file, size, TRUE) : truncating string with embedded nuls
4: In readChar(file, size, TRUE) : truncating string with embedded nuls
5: In readChar(file, size, TRUE) : truncating string with embedded nuls
6: In readChar(file, size, TRUE) : truncating string with embedded nuls

library(DBI)
library(RSQLite)
library(readr)
library(stringr)
library(ggplot2)
library(dplyr)
library(purrr)
library(tidyr)
library(magrittr)

set working directory

setwd("C:/Users/Ana/Desktop/Data Analytics/CSV Files")
conn <- dbConnect(SQLite(), "chinook.db")
dbListTables(conn)

 [1] "album"          "artist"         "customer"       "employee"       "genre"          "invoice"       
 [7] "invoice_line"   "main_part"      "mainpart1"      "media_type"     "playlist"       "playlist_track"
[13] "track"         

#Send and Fetch Query

query_invoice <- "SELECT * 
            FROM invoice 
            LIMIT 10;
            "
conn <- dbConnect(SQLite(), "chinook.db")
result <- dbSendQuery(conn, query_invoice)
invoice_1 <- dbFetch(result)
dbDisconnect(conn)

There are 1 result in use. The connection will be released when they are closed

invoice_1

#Get Query

conn <- dbConnect(SQLite(), "chinook.db")
invoice <- dbGetQuery(conn, "SELECT * from invoice LIMIT 10")
dbDisconnect(conn)

invoice

Creating the helper functions

db <- 'chinook.db'
run_query <- function(q) {
  conn <- dbConnect(SQLite(), db)
  result <- dbGetQuery(conn, q)
  dbDisconnect(conn)
  return(result)
}

show_tables <- function() {
  q = "SELECT name, type FROM sqlite_master WHERE type IN ('table', 'view')"
  return(run_query(q))
}

show_tables()

Looking at the most popular genres:

genre_query <-  "SELECT g.genre_id, 
                        g.name genre, 
                        SUM(il.quantity) quantity, 
                        ROUND(CAST(SUM(il.quantity) AS FLOAT)/(SELECT sum(quantity) FROM invoice_line)*100, 1)                                   percent
                FROM genre g
                LEFT JOIN track t ON t.genre_id = g.genre_id
                LEFT JOIN invoice_line il ON il.track_id = t.track_id
                GROUP BY genre
                ORDER BY quantity DESC
                "
genre_db <- run_query(genre_query)
genre_db

NA

ggplot(data = genre_db,
        aes(x = genre, y = quantity)) +
        theme(axis.text.x = element_text(angle=60, hjust=1)) +
        geom_bar(stat='identity')+
        labs(title = "No. of tracks purchased of each genre", x = "Genre", y = "No. of tracks purchased") +
        theme(panel.background = element_rect(fill = "white")) +
        geom_text(aes(label = percent, y = quantity+100))

NA
NA

The most popular genre is Rock which accounts for 55% of all tracks purchased. The next monst popular is metal (13%) then Alternative & Punk (10.3%). The new artists with their music genre are provided below along with the corresponding genre percent share: Regal - Hip-hop 0.7% Red Tone - Punk 10.3% Meteor and the Girls - Pop 1.3% Slim Jim Bites - Blues 2.6%. Hip Hop is the least popular, so we should remove Regal and keep the other three artists’ albums.

Looking at the performance of the sales staff:

sales_query <-  "SELECT e.employee_id,
                        e.first_name || ' ' || e.last_name  AS name,
                        e.reports_to,
                        e.hire_date,
                        e.country,
                        SUM(total) total_sales
                FROM employee e
                LEFT JOIN customer c ON c.support_rep_id = e.employee_id
                LEFT JOIN invoice i ON i.customer_id = c.customer_id
                GROUP BY e.employee_id
                "

sales_db <- run_query(sales_query)
sales_db

ggplot(data = sales_db,
        aes(x = name, y = total_sales)) +
        theme(axis.text.x = element_text(angle=60, hjust=1)) +
        geom_bar(stat='identity')+
        labs(title = "Total sales by each employee", x = "Employee", y = "Total Sales per Employee") +
        theme(panel.background = element_rect(fill = "white")) +
        geom_text(aes(label = total_sales, y = total_sales+70))

NA
NA

There are only 3 employees that area Sales Support Reps. Jane Peacock has had the most sales, she joined the company in April 2017. Margaret Park has the second most sales, she joined in May 2017. Steve Johnson has the least sales, but he didn’t join until October 2017. All three have the same supervisor. As there are only 3 support reps, there isn’t enough data to be able to conclude whether any other factor affects how well they perform.

Looking at sales data in different countries:

#The first part of this SQL code looks at the customer table only and replaces the country name with "other" if there is only one customer from a country.
#The next part of the code contains a sub-query (which does most of the work so I'll explain the sub-query first)
#The sub-query joins the customer table to the invoice table. It also creates a new 'logic' column using CASE for when country is "Other".
#The main query then takes all of the columns from the subquery except the logic column and reorders by the logic column.

sales_by_country = '

WITH revised AS (
                SELECT 
                  CASE 
                  WHEN (
                        SELECT count(*)
                        FROM customer
                        WHERE country = c.country
                        ) = 1 THEN "Other"
                  ELSE c.country
                  END AS country_new,
                  c.customer_id
                FROM customer c
                )

SELECT country,
       no_customers,
       total_sales,
       avg_cust_spend,
       avg_order_value
FROM (
      SELECT  r.country_new country,
              COUNT(DISTINCT(r.customer_id)) no_customers,
              SUM(i.total) total_sales,
              ROUND(SUM(i.total)/COUNT(DISTINCT(r.customer_id)),2) avg_cust_spend,
              ROUND(avg(i.total),2) avg_order_value,
              CASE
              WHEN r.country_new = "Other" THEN 1
              ELSE 0
              END AS logic
      FROM revised r
      LEFT JOIN invoice i ON i.customer_id = r.customer_id
      GROUP BY country_new
      ORDER BY total_sales DESC
      )
ORDER BY logic 
;
'

country_db <- run_query(sales_by_country)
country_db

NA
NA
NA

#remove the entry for Country = 'Other' 

country_db <- country_db %>%
  filter(country != "Other")

ggplot(data = country_db,
        aes(x = country, y = no_customers, fill = country)) +
        theme(axis.text.x = element_text(angle=60, hjust=1)) +
        geom_bar(stat='identity')+
        labs(title = "No. Customers per Country", x = "Country", y = "No. Customers") +
        theme(panel.background = element_rect(fill = "white")) +
        geom_text(aes(label = no_customers, y = no_customers+0.5))

      
ggplot(data = country_db,
        aes(x = country, y = total_sales, fill = country)) +
        theme(axis.text.x = element_text(angle=60, hjust=1)) +
        geom_bar(stat='identity') +
        labs(title = "Total Sales per Country", x = "Country", y = "Total Sales($)") +
        theme(panel.background = element_rect(fill = "white")) +
        geom_text(aes(label = total_sales, y = total_sales+50))

ggplot(data = country_db,
        aes(x = country, y = avg_cust_spend, fill = country)) +
        theme(axis.text.x = element_text(angle=60, hjust=1)) +
        geom_bar(stat='identity') +
        labs(title = "Average Customer Spend per Country", x = "Country", y = "Average Customer Spend($)") +
        theme(panel.background = element_rect(fill = "white")) +
        geom_text(aes(label = avg_cust_spend, y = avg_cust_spend+5))

ggplot(data = country_db,
        aes(x = country, y = avg_order_value, fill = country)) +
        theme(axis.text.x = element_text(angle=60, hjust=1)) +
        geom_bar(stat='identity') +
        labs(title = "Average Order Value per Country", x = "Country", y = "Average Order Value($)") +
        theme(panel.background = element_rect(fill = "white")) +
        geom_text(aes(label = avg_order_value, y = avg_order_value+0.5))

The greatest total sales is from the US market followed by Canada. However, the average customer spend in Canada is the lowest out of all the countries (exc other). Some target marketing at existing customers could increase the spend per customer and hence increase total sales. The Czech Republic has only 2 customers but the average customer spend is the highest. Could some target marketing in the Czech Replublic reap rewards with high spenders?

Looking at what percentage of sales are ‘whole album purchases’:

I will calculate what percentage of all purchases are ‘whole album purchases’ i.e the customer selects and buys a whole album, rather than selects a number of tracks (from different albums)

#My solution uses a completely different method from what was suggested in the guidance notes.
#My method is based on the fact that if a customer buys a whole album then these 2 things will be true:
# 1 - There will only be 1 distinct album number per invoice i.e. when you COUNT(DISTINCT album_id) for each invoice it will return 1
# 2 - The number of tracks purchased will equal the number of tracks on the album of the first album number on their invoice
#If both these things are true, then the customer purchased an album.

#The query is split into a first part - a_t_nos (album track numbers), then the main part which includes a subquery.

#The first part takes the album table and joins it to the track table. It then groups by album_id and counts the number of tracks so the result is a table a_t_nos of albums and the corresponding number of tracks on each album.

#The main part, starting with the subquery, then takes the invoice_line table and joins it to the track table and then joins the a_t_nos table onto that. It groups by invoice_line. For each invoice_line, it calculates:
# - the number of tracks bought
# - the number of albums on each invoice i.e. COUNT(DISTINCT album_id) 
# - it leaves the columns invoice_id, album_id and tracks_in_album
#It then adds a logic column called 'album'. This column is given the value of 1 if BOTH of these two conditions are satisfied:
# 1. Is the number of distinct albums on this invoice 1? 
# 2. Is the number of tracks on the invoice equal to the number of tracks in the album number?
#If they're both satisfied, then it's an album!

#Lastly, the encompassing query sums the number of instances where the album column = 1 (i.e. this is the number of invoices which relate to whole album purchases by customers). It counts the album column (i.e. this is the total number of invoices). It calculates the percentage of invoices which are whole-album purchases.

is_it_an_album = '
WITH a_t_nos AS
              (
              SELECT  a.album_id,
                      t.track_id,
                      COUNT(t.track_id) tracks_in_album
              FROM album a
              INNER JOIN track t ON t.album_id = a.album_id
              GROUP BY a.album_id
              )

SELECT  SUM(album) invoices_for_albums,
        COUNT(album) all_invoices,
        ROUND(CAST(SUM(album) AS FLOAT)/COUNT(album), 2) percent_invoices_for_albums
FROM(
    SELECT  il.invoice_id,
            COUNT(il.track_id) tracks_bought,
            COUNT(DISTINCT t.album_id) sole_album_check,
            atn.album_id,
            atn.tracks_in_album,
            CASE
              WHEN COUNT(DISTINCT t.album_id) = 1 AND COUNT(il.track_id) = atn.tracks_in_album THEN 1
              ELSE 0
              END AS album
        
    FROM invoice_line il
    INNER JOIN track t ON t.track_id = il.track_id
    LEFT JOIN a_t_nos atn ON atn.album_id = t.album_id
    GROUP BY invoice_id
  )
;
'

run_query(is_it_an_album)

NA

Of all purchases, 19% are whole album purchases. This is quite a large proportion of total purchases so it wouldn’t be advisable to change strategy.

My method was different from the suggested method. Here is the model answer showing the suggested method which uses EXCEPT to run 2 lists against each other to check if they are the same. I have commented the code.

#The first part of the code takes the invoice_id and calculates the first_track_id (using MIN) from each invoice and puts this in a table called invoice_first_track.

#The next bit in the code is actually the last bit to be calculated so I'll skip this for now. See [1].

#The 'main bit' of the code takes the table invoice_first_track (with alias ift) and selects all the columns (ifs.*) plus it adds a case column called album_purchase which it asigns a YES or NO to.
#Within the 'CASE' part:

#selects all the tracks from the album number corresponding to the album number from the first_track_id column in ifs
#so that's the 'true album list'
#next it needs to compare that to the list of tracks on that invoice. i.e. it takes the track_id from il where invoice_id corresponds to the ifs invoice_id.
#It checks if it returns NULL
#Then it also has to do it the other way round
#they both have to return NULL
#If they both return NULL then a 'yes' is put in the "album_purchase" column. ELSE NO. 

#[1] Lastly go back up to the code from before which is the last bit to be calculated. This calculates columns album_purchase, number_of_invoices and percent


albums_vs_tracks = '
WITH invoice_first_track AS
    (
     SELECT
         il.invoice_id invoice_id,
         MIN(il.track_id) first_track_id
     FROM invoice_line il
     GROUP BY 1
    )
    
SELECT
    album_purchase,
    COUNT(invoice_id) number_of_invoices,
    CAST(count(invoice_id) AS FLOAT) / (
                                         SELECT COUNT(*) FROM invoice
                                      ) percent
FROM
    (
    SELECT
        ifs.*,
        CASE
            WHEN
                 (
                  SELECT t.track_id FROM track t
                  WHERE t.album_id = (
                                      SELECT t2.album_id FROM track t2 
                                      WHERE t2.track_id = ifs.first_track_id
                                     ) 
                  EXCEPT 
                  SELECT il2.track_id FROM invoice_line il2
                  WHERE il2.invoice_id = ifs.invoice_id
                 ) IS NULL
             AND
                 (
                  SELECT il2.track_id FROM invoice_line il2
                  WHERE il2.invoice_id = ifs.invoice_id
                  EXCEPT 
                  SELECT t.track_id FROM track t
                  WHERE t.album_id = (
                                      SELECT t2.album_id FROM track t2
                                      WHERE t2.track_id = ifs.first_track_id
                                     ) 
                 ) IS NULL
             THEN "yes"
             ELSE "no"
         END AS "album_purchase"
     FROM invoice_first_track ifs
    )
GROUP BY album_purchase;
'
run_query(albums_vs_tracks)

NA