---
title: "Answering Business Questions using SQL (Intermediate SQL in R): Guided Project Solutions"
output: html_document
---

# Creating Helper Functions

```{r}
library(RSQLite)
library(DBI)

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()
```

# Selecting New Albums to Purchase

```{r}
albums_to_purchase = '
WITH usa_tracks_sold AS
   (
    SELECT il.* FROM invoice_line il
    INNER JOIN invoice i on il.invoice_id = i.invoice_id
    INNER JOIN customer c on i.customer_id = c.customer_id
    WHERE c.country = "USA"
   )
SELECT
    g.name genre,
    count(uts.invoice_line_id) tracks_sold,
    cast(count(uts.invoice_line_id) AS FLOAT) / (
        SELECT COUNT(*) from usa_tracks_sold
    ) percentage_sold
FROM usa_tracks_sold uts
INNER JOIN track t on t.track_id = uts.track_id
INNER JOIN genre g on g.genre_id = t.genre_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
'

run_query(albums_to_purchase)
```

```{r}
library(ggplot2)
genre_sales = run_query(albums_to_purchase)
ggplot(data = genre_sales, aes(x = reorder(genre, -percentage_sold), 
                               y = percentage_sold)) +
  geom_bar(stat = "identity")
```

Among the genres represented in our list of 4 albums, punk, blues and pop are the highest rated. Therefore, we should recommend:

- Red Tone (Punk)
- Slim Jim Bites (Blues)
- Meteor and the Girls (Pop)

By far though, rock makes up the majority of the sales. To better capture sales in the USA, we might want to ask the record label if they have any up-and-coming rock bands.

# Analyzing Employee Sales Performance

```{r}
employee_sales_performance = '
WITH customer_support_rep_sales AS
    (
     SELECT
         i.customer_id,
         c.support_rep_id,
         SUM(i.total) total
     FROM invoice i
     INNER JOIN customer c ON i.customer_id = c.customer_id
     GROUP BY 1,2
    )

SELECT
    e.first_name || " " || e.last_name employee,
    e.hire_date,
    SUM(csrs.total) total_sales
FROM customer_support_rep_sales csrs
INNER JOIN employee e ON e.employee_id = csrs.support_rep_id
GROUP BY 1;
'

run_query(employee_sales_performance)
```

```{r}
employee_sales = run_query(employee_sales_performance)
ggplot(data = employee_sales, aes(x = reorder(employee, -total_sales), 
                               y = total_sales)) +
  geom_bar(stat = "identity")
```

Jane Peacock has the highest amount of sales, but she also has been at the company the longest. If we really want to hone in on employee efficiency, we might want to standardize sales by the number of days or hours worked. 

# Visualizing Sales by Country

```{r}
sales_by_country = '
WITH country_or_other AS
    (
     SELECT
       CASE
           WHEN (
                 SELECT count(*)
                 FROM customer
                 where country = c.country
                ) = 1 THEN "Other"
           ELSE c.country
       END AS country,
       c.customer_id,
       il.*
     FROM invoice_line il
     INNER JOIN invoice i ON i.invoice_id = il.invoice_id
     INNER JOIN customer c ON c.customer_id = i.customer_id
    )
SELECT
    country,
    customers,
    total_sales,
    average_order,
    customer_lifetime_value
FROM
    (
    SELECT
        country,
        count(distinct customer_id) customers,
        SUM(unit_price) total_sales,
        SUM(unit_price) / count(distinct customer_id) customer_lifetime_value,
        SUM(unit_price) / count(distinct invoice_id) average_order,
        CASE
            WHEN country = "Other" THEN 1
            ELSE 0
        END AS sort
    FROM country_or_other
    GROUP BY country
    ORDER BY sort ASC, total_sales DESC
    );
'

run_query(sales_by_country)
```

# Visualizing Sales by Country

```{r}
country_metrics = run_query(sales_by_country)

ggplot(data = country_metrics, aes(x = reorder(country, -total_sales), 
                                   y = total_sales, 
                                   fill = country)) +
  geom_bar(stat = "identity") +
  labs(
    title = "Total sales by country",
    x = "Country",
    y = "Total Sales"
  ) + theme(axis.text.x = element_text(angle = 45, hjust = 1))

ggplot(data = country_metrics, aes(x = reorder(country, -customers), 
                                   y = customers, 
                                   fill = country)) +
  geom_bar(stat = "identity") +
  coord_polar("y") + 
  labs(
    title = "Number of customers by country",
    x = "Country",
    y = "Customers"
  )

ggplot(data = country_metrics, aes(x = reorder(country, -customer_lifetime_value), 
                                   y = customer_lifetime_value, 
                                   color = country)) +
  geom_point(stat = "identity") +
  labs(
    title = "Customer lifetime value by country",
    x = "Country",
    y = "Customer Lifetime Value"
  ) + theme(axis.text.x = element_text(angle = 45, hjust = 1))
```

# Albums vs Individual Tracks

```{r}
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)
```

Album purchases account for almost a quarter of the total sales, so it is inadvisable to change strategy to just purchase the most popular tracks.