Guided Project: Answering Business Questions using SQL

Creating Helper Functions

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

## [1] name type
## <0 rows> (or 0-length row.names)

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;
'