Posted by Cyn, Feb. 1, 2022, 10:30 p.m.
#14 why my subquery returns only 7 results?
Hi, my subquery only returns dates between 5/25 and 5/31 while its supposed to spit out every single day in May. I don't know what I did wrong. Any help is appreciated. Thx!
WITH cte AS (
SELECT DATE(rental_ts) AS dt, COUNT(rental_id) AS rentalcounts FROM rental
WHERE EXTRACT(YEAR FROM rental_ts) = '2020' AND EXTRACT(MONTH FROM rental_ts) = '05'
GROUP BY DATE(rental_ts))
SELECT (SELECT COUNT(*) FROM cte WHERE rentalcounts > 100) AS good_days,
(SELECT COUNT(*) FROM cte WHERE rentalcounts <= 100) AS bad_days
WITH cte AS (
SELECT DATE(rental_ts) AS dt, COUNT(rental_id) AS rentalcounts FROM rental
WHERE EXTRACT(YEAR FROM rental_ts) = '2020' AND EXTRACT(MONTH FROM rental_ts) = '05'
GROUP BY DATE(rental_ts))
SELECT (SELECT COUNT(*) FROM cte WHERE rentalcounts > 100) AS good_days,
(SELECT COUNT(*) FROM cte WHERE rentalcounts <= 100) AS bad_days
