Write monthly new-vs-returning requests SQL

Given the schema and sample data below, write a single PostgreSQL query (no dynamic SQL) that returns, for every calendar month present in requests, the counts and shares of requests from NEW vs RETURNING users. Definitions: A user's FIRST_REQUEST_MONTH is date_trunc('month', MIN(request_ts)) over all their requests. A request is NEW if date_trunc('month', request_ts) = FIRST_REQUEST_MONTH for that user; otherwise it is RETURNING. Output columns: month (YYYY-MM), new_request_count, returning_request_count, new_share_pct (0–100 with two decimals), returning_share_pct. Requirements: (1) Include months even if one class has zero; (2) Avoid double counting; (3) Treat timestamps as UTC; (4) Do not assume contiguous months; (5) Be robust if a user exists in users but has no requests (they should not appear unless the month has requests from someone); (6) Order by month ascending. Also, show what your query would output on the sample data to verify understanding.

Schema:

users(user_id INT PRIMARY KEY, signup_dt DATE)
requests(request_id INT PRIMARY KEY, user_id INT REFERENCES users(user_id), request_ts TIMESTAMP NOT NULL)

Sample tables:

users
+---------+------------+
| user_id | signup_dt |
+---------+------------+
| 1 | 2025-01-10 |
| 2 | 2025-01-20 |
| 3 | 2025-02-02 |
| 4 | 2025-02-25 |
+---------+------------+

requests
+------------+---------+---------------------+
| request_id | user_id | request_ts |
+------------+---------+---------------------+
| 101 | 1 | 2025-01-11 08:00:00 |
| 102 | 1 | 2025-02-01 10:00:00 |
| 103 | 2 | 2025-01-25 12:00:00 |
| 104 | 2 | 2025-02-15 09:00:00 |
| 105 | 3 | 2025-02-10 14:00:00 |
| 106 | 3 | 2025-02-28 16:00:00 |
| 107 | 1 | 2025-03-05 11:00:00 |
| 108 | 4 | 2025-03-01 07:30:00 |
+------------+---------+---------------------+

Monthly New vs Returning Request Shares

Using PostgreSQL, write a single query (no dynamic SQL) that returns, for every calendar month present in the requests table, the counts and shares of requests from NEW vs RETURNING users.

Definitions:

• A user's FIRST_REQUEST_MONTH is date_trunc('month', MIN(request_ts)) over all their requests.
• A request is NEW if date_trunc('month', request_ts) = FIRST_REQUEST_MONTH for that user; otherwise it is RETURNING.

Output columns:

• month (formatted as 'YYYY-MM')
• new_request_count
• returning_request_count
• new_share_pct (0–100 with two decimals)
• returning_share_pct (0–100 with two decimals)

Requirements:

1. Include all months that appear in requests, even if one class (NEW or RETURNING) has zero requests.
2. Avoid double counting; each request must be classified as exactly NEW or RETURNING.
3. Treat timestamps as UTC.
4. Do not assume months are contiguous; just report the months that exist in the data.
5. Be robust if a user exists in users but has no requests (such a user should not appear unless the month has requests from someone).
6. Order the output by month ascending.

Also, show what your query would output on the sample data below to verify understanding (this is represented in the expected_result section).

Table users

column	type	constraints
user_id	INT	PRIMARY KEY
signup_dt	DATE	NOT NULL

Sample data:

signup_dt	user_id
2025-01-10	1
2025-01-20	2
2025-02-02	3
2025-02-25	4

Table requests

column	type	constraints
request_id	INT	PRIMARY KEY
user_id	INT	NOT NULL, REFERENCES users(user_id)
request_ts	TIMESTAMP	NOT NULL

Sample data:

request_id	request_ts	user_id
101	2025-01-11 08:00:00	1
102	2025-02-01 10:00:00	1
103	2025-01-25 12:00:00	2
104	2025-02-15 09:00:00	2
105	2025-02-10 14:00:00	3
106	2025-02-28 16:00:00	3
107	2025-03-05 11:00:00	1
108	2025-03-01 07:30:00	4

Expected output

For each month present in the requests table, return one row with the month (YYYY-MM), the number of NEW and RETURNING requests, and their percentage shares (0–100, two decimals). On the sample data, the result would be:

• 2025-01: 2 NEW, 0 RETURNING (100.00% NEW, 0.00% RETURNING)
• 2025-02: 2 NEW, 2 RETURNING (50.00% NEW, 50.00% RETURNING)
• 2025-03: 1 NEW, 1 RETURNING (50.00% NEW, 50.00% RETURNING)

month	new_request_count	new_share_pct	returning_request_count	returning_share_pct
2025-01	2	100	0	0
2025-02	2	50	2	50
2025-03	1	50	1	50

Hints

1. First compute each user's first_request_month using MIN(request_ts) OVER (PARTITION BY user_id).
2. Classify each request as NEW or RETURNING with a CASE expression, then aggregate counts and percentages per month.