SQLPad
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:> 100rentals.bad day:<= 100rentals.- Hint (For users already know
OUTER JOIN), you can usedatestable - Hint: be super careful about datetime columns.
- Hint: this problem could be tricky, feel free to explore the
rentaltable 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".