Table: 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
Question 15
Fast movie watchers vs slow watchers
Instruction
- Write a query to return the number of fast movie watchers vs slow movie watchers.
fast movie watcher: by average return their rentals within 5 days.slow movie watcher: takes an average of>5days to return their rentals.- Most customers have multiple rentals over time, you need to first compute the number of days for each rental transaction, then compute the average on the rounded up days. e.g., if the rental period is 1 day and 10 hours, count it as 2 days.
- Skip the rentals that have not been returned yet, e.g.,
return_ts IS NULL. - The orders of your results doesn't matter.
- A customer can only rent one movie per transaction.
Sample results
watcher_category | count ------------------+------- fast_watcher | 112 slow_watcher | 487
Solution
postgresWITH average_rental_days AS (
SELECT
customer_id,
AVG(EXTRACT(days FROM (return_ts - rental_ts) ) + 1) AS average_days
FROM rental
WHERE return_ts IS NOT NULL
GROUP BY 1
)
SELECT CASE WHEN average_days <= 5 THEN 'fast_watcher'
WHEN average_days > 5 THEN 'slow_watcher'
ELSE NULL
END AS watcher_category,
COUNT(*)
FROM average_rental_days
GROUP BY watcher_category;Explanation
This query calculates the average number of days a customer rents a movie and then categorizes them as either a "fast watcher" or a "slow watcher."
The first part of the query creates a temporary table called "average_rental_days," which calculates the average number of days each customer rented movies for. It only includes rentals that have been returned (return_ts is not null) and groups the results by customer_id.
The second part of the query uses a CASE statement to categorize the customers based on their average rental days. Customers who rent for 5 days or less are categorized as "fast watchers," while those who rent for more than 5 days are categorized as "slow watchers." Any customers with a null value for average_days are ignored.
Finally, the query counts the number of customers in each category and groups them by the watcher_category.
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.
15:00
Your results
Run your query to preview results here.