SQL Business Analyst Project: Selecting Albums to Purchase

Screen Link:

My Code:

library(DBI)
library(dplyr)
library(RSQLite)
library(ggplot2)
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()

albums_to_purchase ='  
WITH usa_track AS 
(
  SELECT * FROM invoice i
  INNER JOIN invoice_line il ON i.invoice_id = il.invoice_id
  WHERE i.billing_country="USA"
  )
SELECT 
  g.name genre,
  COUNT(ut.invoice_line_id) tracks_sold,
  CAST(COUNT(ut.invoice_line_id) AS FLOAT)/( 
    SELECT COUNT(*) FROM usa_track) 
    percentage_sold
  FROM usa_track ut
  INNER JOIN track t ON t.track_id=ut.track_id
  INNER JOIN genre g ON g.genre_id = t.genre_id
GROUP BY genre
ORDER BY tracks_sold DESC 
LIMIT 10;
' 
run_query(albums_to_purchase)

What I expected to happen:
I expected the query to run and return every genre with the number of tracks sold in the USA.

What actually happened:
I receive an error message about how invoice doesn’t exist.

Error: no such table: invoice
In addition: Warning message:
 Error: no such table: invoice 

I at first tried the answer given in the solution key and received the same error message for invoice_line.

Hi I got the same problem. I tried to copy the solution key like you and got the same error report. Did you find the problem?

No. I haven’t found the problem.

I found there are other people also asked the similar question and the dataquest assistant replied the following solutions:
I worked on this project recently. May be these tips could help.

  • I created a folder/directory for the project
  • I put all the files that I needed in this folder/directory. In this case, it is chinook.db and chinook-unmodified
  • I opened the R Markdown or Notebook. And I saved in the same folder/directory
  • Then I did Session > Set Working Directory > To Source File Location
  • Then I ran these library(RSQLite) and library(DBI)

These steps worked for me.