Analyze Posting Behavior by Cohort and Date

creator_post

+------------+---------+------+
| post_date  | user_id | post |
+------------+---------+------+
| 2023-01-01 | 111     | 1    |
| 2023-01-01 | 222     | 2    |
| 2023-01-02 | 111     | 4    |
| 2022-01-03 | 333     | 10   |
+------------+---------+------+

Scenario

Product analytics team wants to understand posting behavior of content creators by acquisition cohort.

Question

Write SQL to assign each user to a cohort defined by their first post_date (first day they ever posted). For each cohort and each posting_date, return total posts and distinct active users. Order results by cohort_start_date and posting_date.

Hints

Use window functions or sub-query to get users’ MIN(post_date); join back to original table for aggregation.

Cohort Posting Metrics by First Post Date

You are given a table of creator posts. Assign each user to a cohort defined by their first post_date (the first day they ever posted). For each cohort and each posting_date, return the total number of posts and the number of distinct active users from that cohort on that date. Order the results by cohort_start_date and posting_date.

Table creator_post

column	type	constraints
post_date	DATE	NOT NULL
user_id	INTEGER	NOT NULL
post	INTEGER	NOT NULL

Sample data:

post	post_date	user_id
1	2023-01-01	111
2	2023-01-01	222
4	2023-01-02	111
10	2022-01-03	333

Expected output

For each cohort (defined as the first post_date per user) and each posting_date, return the total number of posts made by users in that cohort on that date and the count of distinct active users. Results are ordered by cohort_start_date and then posting_date.

active_users	cohort_start_date	posting_date	total_posts
1	2022-01-03	2022-01-03	10
2	2023-01-01	2023-01-01	3
1	2023-01-01	2023-01-02	4

Hints

1. Compute each user’s cohort as MIN(post_date) using a window function or a grouped subquery/CTE.
2. Join the cohort mapping back to creator_post, then aggregate by cohort_start_date and post_date with SUM(post) and COUNT(DISTINCT user_id).