Instruction
- Write a query to return the number of busy days and slow days in May 2020 based on the number of movie rentals.
- The order of your results doesn't matter.
- If there are ties, return just one of them.
Definition
- busy: rentals
>= 100. - slow: rentals
< 100.
Table 1: dates
Calendar dates from 01/01/2019 to 12/31/2025.
col_name | col_type ----------+---------- year | smallint month | smallint date | date
Table 2: 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
date_category | count ---------------+------- busy | 10 slow | 21
Expected results
Solution postgres
SELECT date_category, COUNT(*)
FROM (
SELECT D.date,
CASE WHEN COUNT(*) >= 100 THEN 'busy' ELSE 'slow' END date_category
FROM dates D
LEFT JOIN (
SELECT * FROM rental
) R
ON D.date = DATE(R.rental_ts)
WHERE D.date >= '2020-05-01'
AND D.date <= '2020-05-31'
GROUP BY D.date
) X
GROUP BY date_category
;
Explanation
This query is essentially counting the number of "busy" and "slow" days in a given month based on the number of rentals that occurred on each day.
The query first selects all dates within the month of May 2020 from a table called "dates". It then joins this table with another table called "rental" using the rental timestamp as the join condition.
Next, it groups the rentals by date and counts the number of rentals on each day. If a day has 100 or more rentals, it is classified as "busy". Otherwise, it is classified as "slow".
Finally, the query groups the results by the "date_category" (i.e. "busy" or "slow") and counts the number of days in each category.
Overall, this query provides a simple way to categorize the activity level of each day in a given month based on rental data.