SQL

con <- dbConnect(
  RMariaDB::MariaDB(),
  host = "relational.fit.cvut.cz",
  port = 3306,
  username = "guest",
  password = "relational",
  dbname = "sakila"
)
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    COUNT(r.rental_id) AS total_rented_films
FROM
    customer c
JOIN rental r ON c.customer_id = r.customer_id
GROUP BY
    c.customer_id, c.first_name, c.last_name, c.email
ORDER BY
    total_rented_films DESC
LIMIT 10;
Displaying records 1 - 10
customer_id first_name last_name email total_rented_films
148 ELEANOR HUNT 46
526 KARL SEAL 45
144 CLARA SHAW 42
236 MARCIA DEAN 42
75 TAMMY SANDERS 41
469 WESLEY BULL 40
197 SUE PETERS 40
468 TIM CARY 39
137 RHONDA KENNEDY 39
178 MARION SNYDER 39 
SELECT film.title,
       film.description
FROM film
LEFT JOIN inventory USING (film_id)
LEFT JOIN rental USING (inventory_id)
WHERE rental.rental_id IS NULL
ORDER BY film.title;
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 
SELECT category.name AS category_name,
       AVG(film.length) AS average_length
FROM film
JOIN film_category USING (film_id)
JOIN category USING (category_id)
GROUP BY category.name
ORDER BY AVG(film.length) DESC;
Displaying records 1 - 10
category_name average_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 
SELECT actor.first_name,
       actor.last_name,
       COUNT(film_actor.film_id) AS film_count
FROM actor
JOIN film_actor USING (actor_id)
GROUP BY actor.actor_id, actor.first_name, actor.last_name
ORDER BY film_count DESC
LIMIT 5;
5 records
first_name last_name film_count
GINA DEGENERES 42
WALTER TORN 41
MARY KEITEL 40
MATTHEW CARREY 39
SANDRA KILMER 37 
SELECT film.title AS film_title,
       SUM(payment.amount) AS total_revenue
FROM film
JOIN inventory USING (film_id)
JOIN rental USING (inventory_id)
JOIN payment USING (rental_id)
GROUP BY film.film_id, film.title
ORDER BY total_revenue DESC
LIMIT 10;
Displaying records 1 - 10
film_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

scatterplot <- plot_ly(data = diamonds, 
                       x = ~depth, 
                       y = ~price, 
                       type = 'scatter', 
                       mode = 'markers')

layout <- list(title = "Diamonds- Depth vs. Price",
               xaxis = list(title = "Depth"),
               yaxis = list(title = "Price"),
               hovermode = "closest")

plot1 <- scatterplot %>% layout(layout)
plot1

  1. When I am trying to show the interactivity of data, explore and interact with data dynamically in a web-based environment, plotly function can provide me a flexible framework for creating animated visualizations, allowing to explore the change and trend over time for my data. For example, when I want to explore the relationship between GPA per capita and life expectancy in the 21 cuntury for each continent, I can use plotly and make a slide that is easier for me to see the change in relationship over years, compare with ggplot.

animated_plot <- gapminder %>%
  plot_ly(
    x = ~pop,  
    y = ~gdpPercap,
    size = ~pop,
    color = ~continent,
    text = ~paste("Country: ", country, "<br>Region: ", continent, "<br>GDP: $", gdpPercap, "<br>Life Expectancy: ", lifeExp, "<br>Population: ", pop),
    marker = list(sizemode = "diameter"),
    frame = ~year
  ) %>%
  layout(
    title = "Gapminder Data",
    xaxis = list(title = "Population"),
    yaxis = list(title = "GDP per Capita"),
    showlegend = TRUE
  )

animated_plot
## 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

This plot could be used to explore how the population size of countries changes concerning their GDP per Capita over time, providing insights into demographic and economic trends across different regions. Based on the result, it is hard to find a overall relationship between population and GDP per Capita. Most European countries have relatively small population but high GDP per Capita. India and China are two countries that have largest population with fastest change over time. United State have a clear positive relationship between population and GDP per Capita.