Count Same-Day Registered Posters

You are given two tables:

users
- user_id: unique identifier for a user
- registered_at: timestamp when the user registered

posts
- post_id: unique identifier for a post
- user_id: identifier of the user who published the post
- created_at: timestamp when the post was published

Write a SQL query that returns, for each calendar date, the number of distinct users who registered on that date and also published at least one post on that same date.

Important detail: a user may publish multiple posts on the same day, but that user should be counted only once for that date.

Expected output:

registration_date | user_count

Count Same-Day Registered Posters

You are given two tables, users and posts.

• users(user_id, registered_at) stores when each user registered.
• posts(post_id, user_id, created_at) stores when each post was published.

For each calendar date, count how many distinct users both:

1. registered on that date, and
2. published at least one post on that same date.

If a user publishes multiple posts on the same day, they must be counted only once for that date.

Return the result sorted by registration_date in ascending order. Dates with no qualifying users should not appear.

Note: The original interview version is a SQL problem. In this coding version, the two tables are provided as Python lists of tuples, and each timestamp is a string in YYYY-MM-DD HH:MM:SS format.

Constraints

• 0 <= len(users), len(posts) <= 200000
• Each user_id in users is unique
• Timestamps are valid strings in YYYY-MM-DD HH:MM:SS format
• A user may publish multiple posts on the same day, but counts only once for that date
• Posts whose user_id does not exist in users should be ignored

Examples

Example 1

Input: ([(1, "2023-07-01 08:00:00"), (2, "2023-07-01 09:30:00"), (3, "2023-07-02 10:00:00")], [(10, 1, "2023-07-01 12:00:00"), (11, 1, "2023-07-01 13:00:00"), (12, 2, "2023-07-02 08:00:00"), (13, 3, "2023-07-02 11:00:00"), (14, 2, "2023-07-01 18:00:00")])
Output: [("2023-07-01", 2), ("2023-07-02", 1)]

Explanation: Users 1 and 2 both registered and posted on 2023-07-01. User 1 posted twice but is counted once. User 3 qualifies on 2023-07-02.

Example 2

Input: ([(1, "2023-01-01 00:00:00"), (2, "2023-01-01 05:00:00"), (3, "2023-01-03 09:00:00"), (4, "2023-01-03 11:00:00")], [(1, 1, "2023-01-01 10:00:00"), (2, 2, "2023-01-02 10:00:00"), (3, 3, "2023-01-03 09:30:00"), (4, 3, "2023-01-03 10:00:00"), (5, 5, "2023-01-03 12:00:00")])
Output: [("2023-01-01", 1), ("2023-01-03", 1)]

Explanation: User 1 qualifies on 2023-01-01. User 3 qualifies on 2023-01-03 and is counted once despite two posts. User 2 posted on a different day, user 4 never posted, and user 5 does not exist in users.

Hints

1. Extract the date part from both timestamps before comparing them.
2. To avoid counting multiple posts from the same user on the same day more than once, store matched users in a set before aggregating.