Posted by Graham, Sept. 23, 2024, 9:09 p.m.
"15. Fast movie watchers vs slow watchers" solution is wrong
The expected result is incorrect as the solution yielding the expected result has a definition for slow watcher as less than or equal to 5 days.
Defintions:
fast movie watcher: by average return their rentals within 5 days.slow movie watcher: takes an average of>5days to return their rentals.
Expected Result:
| watcher_category | count |
|---|---|
| fast_watcher | 112 |
| slow_watcher | 487 |
Solution yielding the expected result:
SELECT watch_speed, COUNT(*) FROM (
SELECT customer_id, CASE WHEN CEIL(AVG(rental_days)) <= 5 then 'slow_watcher' ELSE 'fast_watcher' END AS watch_speed FROM (
SELECT customer_id, return_ts, rental_ts, DATE_PART('day', return_ts - rental_ts) AS rental_days FROM rental
WHERE (return_ts IS NOT NULL)
)
GROUP BY customer_id
)
GROUP BY watch_speedSELECT watch_speed, COUNT(*) FROM (
SELECT customer_id, CASE WHEN CEIL(AVG(rental_days)) <= 5 then 'slow_watcher' ELSE 'fast_watcher' END AS watch_speed FROM (
SELECT customer_id, return_ts, rental_ts, DATE_PART('day', return_ts - rental_ts) AS rental_days FROM rental
WHERE (return_ts IS NOT NULL)
)
GROUP BY customer_id
)
GROUP BY watch_speed
