***********
- *************************************************************************************************
*****************************.- ****
************************************ - *****************************************
- ******************************
************************************. - ********************************************************************************************************************************
Table 1: film
col_name | col_type
----------------------+--------------------------
film_id | integer
title | text
description | text
release_year | integer
language_id | smallint
original_language_id | smallint
rental_duration | smallint
rental_rate | numeric
length | smallint
replacement_cost | numeric
rating | text
Table 2: inventory
Each row is unique, inventoy_id is the primary key of this table.
col_name | col_type --------------+-------------------------- inventory_id | integer film_id | smallint store_id | smallint
Table 3: rental
col_name | col_type --------------+-------------------------- rental_id | integer rental_ts | timestamp with time zone inventory_id | integer customer_id | smallint return_ts | timestamp with time zone staff_id | smallint
Sample results
film_id | num_rentals | quartile
---------+-------------+----------
30 | 9 | 1
20 | 10 | 1
21 | 22 | 4
Solution postgres
WITH movie_rentals AS (
SELECT
F.film_id,
COUNT(*) AS num_rentals,
NTILE(4) OVER(ORDER BY COUNT(*)) AS quartile
FROM rental R
INNER JOIN inventory I
ON I.inventory_id = R.inventory_id
INNER JOIN film F
ON F.film_id = I.film_id
GROUP BY F.film_id
)
SELECT *
FROM movie_rentals
WHERE film_id IN (1,10,11,20,21,30);
Explanation
This query is analyzing movie rentals from a rental database. It creates a temporary table called "movie_rentals" using a common table expression (CTE), which includes the film ID, the number of rentals for each film, and the quartile each film falls into based on the number of rentals.
The main query selects all the columns from the "movie_rentals" table, but only returns the rows where the film ID matches one of the following: 1, 10, 11, 20, 21, 30. In other words, it only shows data for those specific movies.
This query can be useful for analyzing the popularity of certain movies and their rental patterns. The quartile information can be particularly useful for identifying which movies are the most popular or least popular among customers.
Expected results