Question 44
in demand and not in demand movies in May 2020.in-demand: rented >1 times in May 2020.not-in-demand: rented <= 1 time in May 2020.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
demand_category | count -----------------+------- in-demand | 123 not-in-demand | 456
Solution
postgresSELECT demand_category, COUNT(*)
FROM (
SELECT
F.film_id,
CASE WHEN COUNT(R.rental_id) >1 THEN 'in demand' ELSE 'not in demand' END AS demand_category
FROM film F
LEFT JOIN INVENTORY I
ON F.film_id =I.film_id
LEFT JOIN (
SELECT inventory_id, rental_id
FROM rental
WHERE DATE(rental_ts) >= '2020-05-01'
AND DATE(rental_ts) <= '2020-05-31'
) R
ON R.inventory_id = I.inventory_id
GROUP BY F.film_id
)X
GROUP BY demand_category;Explanation
This query is used to count the number of films that were either "in demand" or "not in demand" during May 2020.
The first part of the query is a subquery that joins the film table with the inventory table and the rental table (filtered for rentals between May 1 and May 31). It groups by film_id and assigns a category of "in demand" if the film was rented more than once during that time period, and "not in demand" if it wasn't rented at all or only rented once.
The outer query then takes the results of the subquery and groups them by the demand category, and counts the number of films in each category.
Overall, this query is useful for analyzing film demand during a specific time period and identifying trends in rental patterns.
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Submit a query to compare against expected output.
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
10:00
Run your query to preview results here.