14. Good days and bad days

hard

Instruction

  • Write a query to return the number of good days and bad days in May 2020 based on number of daily rentals.
  • Return the results in one row with 2 columns from left to right: good_days, bad_days.
  • good day: > 100 rentals.
  • bad day: <= 100 rentals.
  • Hint (For users already know OUTER JOIN), you can use dates table
  • Hint: be super careful about datetime columns.
  • Hint: this problem could be tricky, feel free to explore the rental table and take a look at some data.

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

good_days | bad_days
-----------+----------
         7 |       24

Expected results

Solution 1: postgres

-- (For users who already know OUTER JOIN):

WITH daily_rentals AS (
  SELECT  
	  D.date AS dt,
	  COUNT(R.rental_id) AS num_rentals
  FROM dates D
  LEFT JOIN 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
)
SELECT
    SUM(CASE WHEN num_rentals >100 THEN 1 ELSE 0 END) AS good_days,
    SUM(CASE WHEN num_rentals <=100 THEN 1 ELSE 0 END) AS bad_days
FROM daily_rentals;
    

Explanation

This query retrieves the number of daily rentals for each day in May 2020 and categorizes them as either "good" or "bad" days based on whether the number of rentals is greater than 100 or not.

The first part of the query creates a temporary table called "daily_rentals" that uses an outer join to combine a table of dates with a table of rentals. It then filters the results to only include dates in May 2020 and groups the rentals by date.

The second part of the query uses conditional aggregation to count the number of "good" and "bad" days. It sums up the number of days where the number of rentals is greater than 100 and less than or equal to 100 respectively.

Overall, this query could be useful for analyzing rental trends during the month of May and identifying days with high rental demand.

Solution 2: postgres

-- For people following the course and have not learned outer join yet

WITH daily_rentals AS (
	SELECT  
	 DATE(rental_ts) AS dt,
	 COUNT(*) AS num_rentals
	FROM rental
	WHERE DATE(rental_ts) >= '2020-05-01' 
        AND DATE(rental_ts) <= '2020-05-31' 
	GROUP BY dt
)    
SELECT 		    
    SUM(CASE WHEN num_rentals > 100 THEN 1
         ELSE 0 
         END) AS good_days,
    31 - SUM(CASE WHEN num_rentals > 100 THEN 1 -- there were 31 days in May 2020
         ELSE 0 
         END) AS bad_days
FROM daily_rentals;
    

Explanation

This query is calculating the number of "good" and "bad" rental days in May 2020. The data for this calculation is taken from the "rental" table and is grouped by day. The query first creates a common table expression (CTE) named "daily_rentals" that counts the number of rentals for each day in May 2020.

The main query then uses a conditional statement to check if the number of rentals on each day is greater than 100. If it is, then the day is considered a "good" rental day and is counted in the "good_days" column. If it is not, then the day is considered a "bad" rental day and is counted in the "bad_days" column.

The "SUM" function is used to add up the number of "good" and "bad" days over the entire month of May. The result is returned as a single row with two columns: "good_days" and "bad_days".



More IN, BETWEEN, LIKE, CASE WHEN questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
34%
177 Purchases by platform report amazon medium
10%
176 Employees' annual bonus amazon easy
12%
160 Sellers with no sales by day ebay hard
11%
156 Cancellation rate by unbanned users lyft hard
15%
155 Driver with the highest cancellation rate lyft easy
24%
151 Salary report dropbox easy
18%
138 Happy restaurants doordash easy
14%
136 Extremely late orders doordash easy
11%
131 Churned accounts affirm hard
10%
102 Histogram by visit session duration mobile hard
17%
35 Film length report easy
27%
34 Stocked up movies easy
27%
33 Returning customers medium
8%
32 Unpopular movies hard
17%
31 Movies that have not been returned easy
26%
30 Inactive customers in May easy
22%
29 Second highest spend customer medium
14%
28 Film with the second largest cast medium
26%
27 Film with the largest cast easy
27%
26 Second shortest film easy
30%
25 Shortest film easy
46%
24 Films with more than 10 actors medium
19%
22 Average cost per rental transaction easy
42%
15 Fast movie watchers vs slow watchers hard
6%
13 Actors' first name medium
14%
12 Actors' last name ending in 'EN' or 'RY' easy
27%
11 Actors' last name easy
21%