Posted by rocko, June 17, 2024, 10:13 a.m.
Q13 Fast movie watchers vs slow movie watchers. Count isn't matching
Hi all, Below is my answer, but the count isn't matching. Can you please help me with the part that is missing.
SELECT
'slow_watcher' AS watcher_category,
COUNT(*) AS count
FROM
(
SELECT
customer_id,
ROUND(AVG(EXTRACT(EPOCH FROM (return_ts - rental_ts)) / 86400)) AS avg_time_taken
FROM
rental
WHERE
return_ts IS NOT NULL
GROUP BY
customer_id
) AS data
WHERE
avg_time_taken <= 5
UNION ALL
SELECT
'fast_watcher' AS watcher_category,
COUNT(*) AS count
FROM
(
SELECT
customer_id,
ROUND(AVG(EXTRACT(EPOCH FROM (return_ts - rental_ts)) / 86400)) AS avg_time_taken
FROM
rental
WHERE
return_ts IS NOT NULL
GROUP BY
customer_id
) AS data
WHERE
avg_time_taken > 5
Expected :
watcher_category | count ------------------+------- fast_watcher | 112 slow_watcher | 487
I am getting :
| watcher_category | count |
|---|---|
| slow_watcher | 484 |
| fast_watcher | 115 |
thanks :)
