Produce dating profile funnel report by cohort

You work on a dating app. Produce a daily profile-funnel report for 2025-08-25 through 2025-09-01 inclusive, with one row per day, gender, and age_bucket (18–24, 25–34, 35–44, 45+). For each group, compute: profile_creation_cnt, photo_present_rate, avg_completion_rate (filled_fields/total_fields), profile_viewers_cnt (distinct viewers), likes_sent_cnt, match_rate (matches/views). Also output a separate list of user_ids with profiles missing a photo as of 2025-09-01. Assume ages are computed from birthdate at report date. Return two result sets (or two CTE outputs).

Schemas:

• users(user_id INT, gender TEXT, birthdate DATE, country_code CHAR(2), created_at TIMESTAMP)
• profiles(user_id INT, has_photo BOOLEAN, filled_fields INT, total_fields INT, updated_at TIMESTAMP)
• profile_events(event_id INT, event_date DATE, actor_user_id INT, target_user_id INT, event_type TEXT CHECK (event_type IN ('view','like','match')))

Sample rows (subsets):

users
+---------+--------+------------+--------------+
| user_id | gender | birthdate | country_code |
+---------+--------+------------+--------------+
| 10 | F | 1998-05-01 | FR |
| 11 | M | 1990-02-10 | US |
| 12 | F | 1985-11-20 | US |
+---------+--------+------------+--------------+

profiles
+---------+-----------+---------------+-------------+---------------------+
| user_id | has_photo | filled_fields | total_fields| updated_at |
+---------+-----------+---------------+-------------+---------------------+
| 10 | true | 8 | 10 | 2025-08-31 10:00:00 |
| 11 | false | 5 | 10 | 2025-08-31 12:00:00 |
| 12 | true | 10 | 10 | 2025-09-01 09:00:00 |
+---------+-----------+---------------+-------------+---------------------+

profile_events
+----------+------------+---------------+---------------+------------+
| event_id | event_date | actor_user_id | target_user_id| event_type |
+----------+------------+---------------+---------------+------------+
| 9001 | 2025-08-31 | 11 | 10 | view |
| 9002 | 2025-08-31 | 11 | 10 | like |
| 9003 | 2025-08-31 | 10 | 11 | match |
+----------+------------+---------------+---------------+------------+

Clearly state any assumptions (e.g., how to bucket ages, whether match requires prior like).

Daily dating-profile funnel by signup cohort and age bucket

You work on a dating app. Using the schema below, write one PostgreSQL query that returns a single labeled result table containing both:

1. A daily profile-funnel report for report dates from 2025-08-25 through 2025-09-01 inclusive. Treat each report_date as a signup cohort, so include only users whose created_at::date equals that report_date. Group cohort users by gender and age_bucket at the report_date, using 18-24, 25-34, 35-44, and 45+.
2. A list of users whose latest profile as of 2025-09-01 23:59:59 has has_photo = false.

For the funnel rows, return profile_creation_cnt, photo_present_rate, avg_completion_rate, profile_viewers_cnt, likes_sent_cnt, and match_rate. Views and matches count events where the cohort user is the target on their cohort date; likes_sent_cnt counts like events sent by cohort users on their cohort date. Return all rows in one output with a result_set column. Columns that do not apply to a result_set should be NULL.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
gender	TEXT	NOT NULL
birthdate	DATE	NOT NULL
country_code	CHAR(2)	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

birthdate	country_code	created_at	gender	user_id
1998-05-01	FR	2025-08-25 08:00:00	F	10
1990-02-10	US	2025-08-26 09:00:00	M	11
1985-11-20	US	2025-08-20 07:30:00	F	12
2004-09-10	US	2025-08-30 10:00:00	M	13
1975-03-05	GB	2025-08-31 16:00:00	F	14
1999-12-12	GB	2025-09-01 10:00:00	F	16

Table profiles

column	type	constraints
user_id	INT	NOT NULL
has_photo	BOOLEAN	NOT NULL
filled_fields	INT	NOT NULL
total_fields	INT	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Sample data:

filled_fields	has_photo	total_fields	updated_at	user_id
2	false	10	2025-08-25 09:00:00	10
8	true	10	2025-08-31 10:00:00	10
3	false	10	2025-08-26 13:00:00	11
5	false	10	2025-08-31 12:00:00	11
7	true	10	2025-08-20 08:00:00	12
10	true	10	2025-09-01 09:00:00	12
4	false	10	2025-08-30 11:00:00	13
6	true	10	2025-08-31 15:00:00	14
1	false	10	2025-09-01 10:30:00	16

Table profile_events

column	type	constraints
event_id	INT	PRIMARY KEY
event_date	DATE	NOT NULL
actor_user_id	INT	NOT NULL
target_user_id	INT	NOT NULL
event_type	VARCHAR(10)	NOT NULL, CHECK (event_type IN ('view','like','match'))

Sample data:

actor_user_id	event_date	event_id	event_type	target_user_id
11	2025-08-31	9001	view	10
11	2025-08-31	9002	like	10
10	2025-08-31	9003	match	11
11	2025-08-25	9004	view	10
10	2025-08-26	9005	view	11
10	2025-08-26	9006	like	11
10	2025-08-26	9007	match	11
12	2025-08-30	9008	view	13
11	2025-09-01	9009	view	12
11	2025-09-01	9010	like	12
12	2025-09-01	9011	view	11
12	2025-09-01	9012	match	11
13	2025-09-01	9013	view	16
13	2025-09-01	9014	like	16

Expected output

Sample output for the single labeled result table.

age_bucket	avg_completion_rate	gender	likes_sent_cnt	match_rate	photo_present_rate	profile_creation_cnt	profile_viewers_cnt	report_date	result_set	user_id
25-34	0.2000	F	0	0.0000	0.0000	1	1	2025-08-25	daily_profile_funnel	NULL
35-44	0.3000	M	0	1.0000	0.0000	1	1	2025-08-26	daily_profile_funnel	NULL
18-24	0.4000	M	0	0.0000	0.0000	1	1	2025-08-30	daily_profile_funnel	NULL
45+	0.6000	F	0	0.0000	1.0000	1	0	2025-08-31	daily_profile_funnel	NULL
25-34	0.1000	F	0	0.0000	0.0000	1	1	2025-09-01	daily_profile_funnel	NULL
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	missing_photo_users_as_of_2025_09_01	11
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	missing_photo_users_as_of_2025_09_01	13
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	missing_photo_users_as_of_2025_09_01	16

Hints

1. Create the report-date spine first.
2. Use ROW_NUMBER to select the latest profile snapshot for each user/date.
3. Return the funnel and missing-photo rows through one UNION ALL output.