Vignette: create SQL tutorial using data - Githubissues

erictleung / pixarfilms

:movie_camera: R data package to explore Pixar films, the people, and reception data

https://erictleung.com/pixarfilms

Creative Commons Zero v1.0 Universal

15 stars 4 forks source link

Vignette: create SQL tutorial using data #21

Open erictleung opened 2 years ago

erictleung commented 2 years ago

Topics/commands to cover:

SELECT
FROM
WHERE
GROUP BY/HAVING
ORDER BY
LEFT/RIGHT JOIN
CASE WHEN
DISTINCT
Date manipulation?
LAG and LEAD
Running totals https://learnsql.com/blog/what-is-a-running-total-and-how-to-compute-it-in-sql/
INNER JOIN
COUNT/MIN/MAX/AVG
CTEs

Can make similarities with dplyr/tidyverse verbs.

Questions to answer:

Which were Pixar's top movies? By rating? By box office?
Which producer creates the best movies?
What is the average cadence of movies released?
With movies with sequels, what is their cumulative box office sales?
What is Pixar's cumulative sales over time?
What is the distribution of movie genres across Pixar?

Implementation:

dbplyr, to execute SQL code natively
learnr? Needs to Shiny so maybe not immediately