HW 8

library(DBI)
library(dbplyr)
con <- dbConnect(drv = RSQLite::SQLite(),
                 dbname = ":memory:")

customer <- read.csv("customer.csv")
rental <- read.csv("rental.csv")
film <- read.csv("film.csv")
inventory <- read.csv("inventory.csv")
category <- read.csv("category.csv")
film_category <- read.csv("film_category.csv")
actor <- read.csv("actor.csv")
film_actor <- read.csv("film_actor.csv")
payment <- read.csv("payment.csv")

dbWriteTable(conn = con, name = "customer", value = customer)
dbWriteTable(conn = con, name = "rental", value = rental)
dbWriteTable(conn = con, name = "film", value = film)
dbWriteTable(conn = con, name = "inventory", value = inventory)
dbWriteTable(conn = con, name = "category", value = category)
dbWriteTable(conn = con, name = "film_category", value = film_category)
dbWriteTable(conn = con, name = "actor", value = actor)
dbWriteTable(conn = con, name = "film_actor", value = film_actor)
dbWriteTable(conn = con, name = "payment", value = payment)
knitr::opts_chunk$set(connection = "con")

dbListTables(con)

## [1] "actor"         "category"      "customer"      "film"         
## [5] "film_actor"    "film_category" "inventory"     "payment"      
## [9] "rental"

dbListFields(con, "inventory")

## [1] "inventory_id" "film_id"      "store_id"     "last_update"

Question 1

List the top 10 customers who have rented the most films, including their first and last names, email addresses, and the total number of films they have rented.

SELECT b.first_name, b.last_name, b.email, COUNT(a.rental_id) AS total_rentals
FROM rental AS a
JOIN customer AS b ON a.customer_id = b.customer_id
GROUP BY b.customer_id
ORDER BY total_rentals DESC
LIMIT 10;

Displaying records 1 - 10

first_name	last_name	email	total_rentals
ELEANOR	HUNT	ELEANOR.HUNT@sakilacustomer.org	46
KARL	SEAL	KARL.SEAL@sakilacustomer.org	45
MARCIA	DEAN	MARCIA.DEAN@sakilacustomer.org	42
CLARA	SHAW	CLARA.SHAW@sakilacustomer.org	42
TAMMY	SANDERS	TAMMY.SANDERS@sakilacustomer.org	41
WESLEY	BULL	WESLEY.BULL@sakilacustomer.org	40
SUE	PETERS	SUE.PETERS@sakilacustomer.org	40
TIM	CARY	TIM.CARY@sakilacustomer.org	39
MARION	SNYDER	MARION.SNYDER@sakilacustomer.org	39
RHONDA	KENNEDY	RHONDA.KENNEDY@sakilacustomer.org	39

Question 2

Find the titles and descriptions of films that have never been rented, sorted alphabetically by title.

SELECT a.title, a.description
from film as a
left join inventory as b on a.film_id = b.film_id
left join rental as c on b.inventory_id=c.inventory_id
where rental_id IS NULL
order by title asc;

Displaying records 1 - 10

title	description
ACADEMY DINOSAUR	A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
ALICE FANTASIA	A Emotional Drama of a A Shark And a Database Administrator who must Vanquish a Pioneer in Soviet Georgia
APOLLO TEEN	A Action-Packed Reflection of a Crocodile And a Explorer who must Find a Sumo Wrestler in An Abandoned Mine Shaft
ARGONAUTS TOWN	A Emotional Epistle of a Forensic Psychologist And a Butler who must Challenge a Waitress in An Abandoned Mine Shaft
ARK RIDGEMONT	A Beautiful Yarn of a Pioneer And a Monkey who must Pursue a Explorer in The Sahara Desert
ARSENIC INDEPENDENCE	A Fanciful Documentary of a Mad Cow And a Womanizer who must Find a Dentist in Berlin
BOONDOCK BALLROOM	A Fateful Panorama of a Crocodile And a Boy who must Defeat a Monkey in The Gulf of Mexico
BUTCH PANTHER	A Lacklusture Yarn of a Feminist And a Database Administrator who must Face a Hunter in New Orleans
CATCH AMISTAD	A Boring Reflection of a Lumberjack And a Feminist who must Discover a Woman in Nigeria
CHINATOWN GLADIATOR	A Brilliant Panorama of a Technical Writer And a Lumberjack who must Escape a Butler in Ancient India

Question 3

Retrieve a list of film categories and the average length of films in each category, ordered by the average length in descending order. Include the category name and the average length.

SELECT category.name, AVG(length)
FROM film JOIN film_category USING (film_id) JOIN category USING (category_id)
GROUP BY category.name
ORDER BY AVG(length) DESC;

Displaying records 1 - 10

name	AVG(length)
Sports	128.2027
Games	127.8361
Foreign	121.6986
Drama	120.8387
Comedy	115.8276
Family	114.7826
Music	113.6471
Travel	113.3158
Horror	112.4821
Classics	111.6667

Question 4

Identify the 5 actors who have appeared in the most films, along with the number of films they’ve acted in. Include their first and last names.

select actor.first_name, actor.last_name, count(film_actor.film_id) as num_films
from film_actor
join actor on actor.actor_id=film_actor.actor_id
group by film_actor.actor_id, actor.first_name, actor.last_name
order by num_films desc
limit 5;

5 records

first_name	last_name	num_films
GINA	DEGENERES	42
WALTER	TORN	41
MARY	KEITEL	40
MATTHEW	CARREY	39
SANDRA	KILMER	37

Question 5

List the names of customers who have rented at least one film starring the actor ’Johnny Depp.’

Question 6

Find the top 10 earning film titles along with their total revenue (rental fees), sorted by revenue in descending order. Include the film title and total revenue.

select film.title, sum(payment.amount) as total_revenue
from rental
join payment on rental.rental_id=payment.rental_id
join inventory on rental.inventory_id=inventory.inventory_id
join film on inventory.film_id=film.film_id
group by film.title
order by total_revenue desc
limit 10;

Displaying records 1 - 10

title	total_revenue
TELEGRAPH VOYAGE	231.73
WIFE TURN	223.69
ZORRO ARK	214.69
GOODFELLAS SALUTE	209.69
SATURDAY LAMBS	204.72
TITANS JERK	201.71
TORQUE BOUND	198.72
HARRY IDAHO	195.70
INNOCENT USUAL	191.74
HUSTLER PARTY	190.78

Plotly

Question 1

Use the diamonds dataset to create a 2D plotly scatterplot (you can choose the variables). The plot should have a title and subtitle, axis labels, and hover information that is useful.

library(plotly)

## Loading required package: ggplot2

## 
## Attaching package: 'plotly'

## The following object is masked from 'package:ggplot2':
## 
##     last_plot

## The following object is masked from 'package:stats':
## 
##     filter

## The following object is masked from 'package:graphics':
## 
##     layout

fig <- plot_ly(diamonds, x = ~carat, y = ~price, text = ~paste("Price: ", price, '$<br>Cut:', cut))
fig <- fig %>% layout(title = "Scatterplot of Diamonds' Carat and Price",
                      annotations = list(
      list(
        x = 0.5,
        y = .98,
        xref = "paper",
        yref = "paper",
        xanchor = "center",
        yanchor = "bottom",
        text = "Data shows slightly positive relationship between carat and price",
        showarrow = FALSE
      )
    ))
fig

## No trace type specified:
##   Based on info supplied, a 'scatter' trace seems appropriate.
##   Read more about this trace type -> https://plotly.com/r/reference/#scatter

## No scatter mode specifed:
##   Setting the mode to markers
##   Read more about this attribute -> https://plotly.com/r/reference/#scatter-mode

Question 2

Compare and contrast ggplot2 and plotly. What is an example of a time when you would definitely want to use each one over the other?

ggplot2 is better for creating static plots while plotly is better for interactive plots. If I was doing data analysis and exploration, I would use ggplot2. If I was publishing insights on a topic to the public, I would use plotly so viewers can interact with the data and retrieve their own findings.

Question 3

using the gapminder dataset, make a Plotly plot which compares two variables over time. Explain the meaning of this comparison in context and describe what narrative is advanced by this comparison.

library(gapminder)

## Warning: package 'gapminder' was built under R version 4.3.3

life <- gapminder %>%
  group_by(continent, year) %>%
  summarise(avg_life_expectancy = mean(lifeExp))

## `summarise()` has grouped output by 'continent'. You can override using the
## `.groups` argument.

plot <- plot_ly(life, x = ~year, y = ~avg_life_expectancy, color = ~continent, type = 'scatter', mode = 'lines') %>%
        layout(title = "Average Life Expectancy from 1952-2007 by Continent",
               xaxis = list(title = "Year"),
               yaxis = list(title = "Average Life Expectancy"))
plot

This graph means to compare the life expectancies for each continent and how they have varied over time. We can see that Europe and Oceania have consistently had the highest life expectancies on average. However, the most steady and highest rates of increases have been from Asia and Americas. This might advance the narrative that development and infrastructure in these regions will soon catch up to those of Europe.