- 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.
- busy: rentals
- slow: rentals
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
date_category | count ---------------+------- busy | 10 slow | 21
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 ;
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.
More OUTER JOINS questions
|213||Interchange revenue by issuer and merchant category visa||easy||-|
|194||Daily active users report for new market snap||easy||
|191||Number of replies by group twitter||medium||
|172||Comments distribution social||hard||
|168||Average number of streams in the US tiktok||medium||
|140||Daily sales of restaurant 100011 doordash||hard||
|118||Daily bookings in the US airbnb||hard||
|100||Advertiser ROI social||medium||
|56||Total number of actors||easy||
|54||Customer groups by movie rental spend||medium||
|53||Movie groups by rental income||hard||
|52||Movies cast by movie only actors||easy||
|51||Movie only actor||easy||
|45||Movie inventory optimization||hard||
|44||In-demand vs not-in-demand movies||medium||
|43||Customers who rented vs. those who did not||medium||
|42||Films that are in stock vs not in stock||medium||
|41||Productive actors vs less-productive actors||hard||