Hashtag movie is a database with information on the performance of movies rented via streaming.
The database is in Portuguese.
With this data we will be able to understand
- Customer preferences according to gender and location;
- Movie popularity by release period and genre;
- Films with above-average ratings
First step:
I opened my SQL WorkBench.
Then, I created a new database = New Schema
Hashtagmovie
I import the data from my file
I clicked on Hashtagmovie and I’ve seen the 5 tables created
I went down and clicked on Start Import
I clicked after selecting SELECT * FROM tables: rentals, actors, performances, clients, movies:
# ======= PART 1: =======#
# ======= CREATING GROUPINGS =======#
— CASE 1. I should start by doing an analysis to find out the average rental price of movies.
I used SELECT AVG(price_rental) FROM movies;
The average rental price of movies is 2,20 BRL
— CASE 2. Now that I know the average price for renting movies on hashtagmovie, I should go further in my analysis and find out what the average price is for each movie genre.
genero is genre (translating).
In the analysis below, for each genre, I have the average price and the number of movies rented.
Changing the table’s name – AVG(preco_aluguel) to preco_medio and COUNT(*) to qtd_filmes:
preco_medio is medium_price, qtd_filmes is qtd_movies (translating)
It facilitates the comprehension of the column
Let’s round the numbers that are in float in preco_medio:
We only have 1 art film in the catalog, and the and drama has more quantities at a lower price.
Apparently, there is a demand for this type of film.
# ======= PART 2: =======#
# ======= FILTERS IN GROUPINGS =======#
— I should change the CASE 2 query and consider the following scenario:
— CASE 3: Making the same analysis, but considering only the movies with year_release = > ano_lancamento (translating) equal to 2011.
Using WHERE ano_lancamento = 2011
PART 3:
# ======= SUBQUERIES: USING A SELECT INSIDE ANOTHER SELECT =======#
— CASE 4. I need to do a performance analysis of the rentals. To do this, a common analysis is to identify which rentals scored above average.
Translating:
nota = score
rental = alugueis
I’ll want to select the scores above 7.94 – the average
or to be more accurate and automatic:
Translating:
SELECT *
FROM alugueis (rentals)
WHERE score>= (SELECT AVG(score) FROM rentals);
Getting:
“My quantity of clients”: 20
You can check at the left edge of the table.
Keeping the result.
I’ll save my table in my database.
# ======= PART 4: =======#
# ======= Creating Views – CREATE VIEW =======#
— CREATE/DROP VIEW: Saving the result of a query in our database
— CASE 5. Create a view to store the result of the SELECT below.
View will be a table inside my database called result , which will store the SELECT below:
SELECT
genero,
ROUND(AVG(preco_aluguel), 2) AS media_preco,
COUNT(*) AS qtd_filmes
FROM filmes
GROUP BY genero;
Finally,
to delete the View in my database
Confirming View result dropped
Doing a data analysis project to find out the most requested movie genre and the average ratings for each type of movie can be quite an interesting and revealing experience.
-
Data Collection and Preparation: First, I needed to collect relevant data from the « Hashtag Movie » database. This included information about movie rentals, such as movie ID, genre, movie rating, among others.
-
Exploratory Data Analysis: I performed exploratory data analysis to better understand its characteristics. This included visualizing charts to show the distribution of rented movie genres and average ratings for each type of movie.
-
Determining the Most Requested Genre: Using SQL queries, I grouped the data by movie genre and count the number of rentals for each genre. With this, I could determine which genre is most requested by customers.
-
Calculating Average Ratings for Each Type of Movie: I could also calculate the average ratings for each type of movie by grouping the data by movie genre and calculating the average rating.
-
Creating Views: To facilitate future analyses and simplify complex queries, I created views in the database like, creating a view that displays movie rental data with movie genre and average rating for each type of movie.
-
Drop View: I deleted an existing view from the database using the
DROP VIEWstatement. This would be useful if I needed to recreate the view with different parameters or if it was no longer needed.
