SQL - count rows between dynamic number of date ranges-CodePudding

I have a table to store events like this:

| id | title       | start_date              | end_date                |
| 1  | event one   | 2022-05-11 09:00:00.000 | 2022-08-21 09:00:00.000 |
| 2  | event two   | 2022-06-22 15:00:00.000 | 2022-09-23 15:00:00.000 |
| 3  | event three | 2022-07-12 13:00:00.000 | 2022-08-12 13:00:00.000 |
| 4  | event four  | 2022-07-12 13:00:00.000 | 2022-08-12 13:00:00.000 |

and another table to store posts like this:

| id | title      | created_at              |
| 1  | post one   | 2022-07-03 19:38:00.000 |
| 2  | post two   | 2022-08-29 07:12:00.000 |
| 3  | post three | 2022-10-05 17:35:00.000 |
| 3  | post four  | 2022-10-07 20:05:00.000 |

if I want to get count of posts that happened during a single event I can write:

WITH
    event AS (
        SELECT
            start_date
          , end_date
        FROM
            events
        WHERE
            id = 1
    )
SELECT
    COUNT(*)
FROM
    posts
WHERE
      create_at >= (SELECT start_date FROM event)
  AND create_at < (SELECT end_date FROM event)

but how can I get the count of posts that happened during multiple events, when the target events are only known at runtime?

Edit:

the database is PostgresSQL 13

CodePudding user response：

A join plus aggregation approach should work here:

SELECT p.id, p.title
FROM posts p
INNER JOIN events e
    ON p.created_at BETWEEN e.start_date AND e.end_date
GROUP BY p.id, p.title
HAVING COUNT(*) > 1;