Table 1: dates
Calendar dates from 01/01/2019 to 12/31/2025.
col_name | col_type ----------+---------- year | smallint month | smallint date | date
Question 55
Busy days and slow days
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 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
postgresSELECT 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.
Copied
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
10:00
Your results
Run your query to preview results here.