55. Busy days and slow days

medium

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

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.

Last Submission postgres

Expected results



More OUTER JOIN questions

ID Title Level FTPR
194 Daily active users report for new market snap easy
20%
191 Number of replies by group twitter medium
18%
172 Comments distribution facebook hard
13%
168 Average number of streams in the US tiktok medium
13%
140 Daily sales of restaurant 100011 doordash hard
8%
118 Daily bookings in the US airbnb hard
5%
100 Advertiser ROI facebook medium
8%
56 Total number of actors easy
30%
54 Customer groups by movie rental spend medium
20%
53 Movie groups by rental income hard
11%
52 Movies cast by movie only actors easy
11%
51 Movie only actor easy
37%
45 Movie inventory optimization hard
9%
44 In-demand vs not-in-demand movies medium
6%
43 Customers who rented vs. those who did not medium
8%
42 Films that are in stock vs not in stock medium
12%
41 Productive actors vs less-productive actors hard
7%