SELECT booking_date, COUNT( CASE WHEN redemption_date >= booking_date THEN 1 END ) AS number_of_reservations FROM Reservations GROUP BY booking_date
基于更新的描述,我相信这应该能够获得预期的结果:
SELECT DISTINCT
r."booking_date",
(SELECT COUNT(*)
FROM reservations r2
WHERE
r2."booking_date" <= r."booking_date"
AND r2."redemption_date" >= r."booking_date"
) AS number_of_reservations
FROM
Reservations r
ORDER BY r."booking_date"