Write complex SQL for cohorts and retention

Given tables users(uid, created_at, country), orders(order_id, uid, amount, created_at, status), and events(uid, ts, event_type, campaign), write one SQL query that outputs, for each month and country: (1) new users, (2) conversion rate within 7 days of signup, (3) 7-day rolling retention (active on day D and again on D+ 7), (4) GMV excluding refunded/canceled orders, and (5) top campaign by last-touch attribution from events. Handle late-arriving events, deduplicate by (uid, ts, event_type), ensure timezone consistency, and document window-function choices.

Cohort-Level New Users, Conversion, Retention, GMV, and Last-Touch Campaign

You are given three tables:

• users(uid, created_at, country)
• orders(order_id, uid, amount, created_at, status)
• events(uid, ts, event_type, campaign)

Assume all timestamps are stored in UTC.

Write a single SQL query (you may use CTEs) that produces, for each cohort month and country, the following metrics:

1. new_users: number of users whose signup date (users.created_at) falls in that calendar month, grouped by country.

2. conversion_rate_7d: for that cohort, the fraction of users who place at least one order within 7 days (inclusive) of their signup timestamp. A user is considered converted if they have any order (regardless of status) with orders.created_at between users.created_at and users.created_at + 7 days.

3. retention_rate_7d: for that cohort, the fraction of users who have at least one event on their signup calendar date and at least one event exactly 7 days after their signup date. Use events.ts (converted to UTC date) to decide activity days.

4. gmv: total order amount for that cohort, summing orders.amount for orders whose created_at falls in the same calendar month as the user’s signup and whose status is not in ('REFUNDED', 'CANCELED').

5. top_campaign: the campaign with the largest number of converted users in that cohort, under a last-touch attribution rule:

   • Consider only users who converted within 7 days (as defined in #2).
   • For each such user, find their earliest order within 7 days of signup (the conversion order).
   • For that user, look at all marketing events (from events) with non-NULL campaign where events.ts is less than or equal to the conversion order’s timestamp.
   • After deduplicating events by (uid, ts, event_type), define the last-touch campaign as the campaign of the chronologically latest such event.
   • For each cohort month and country, count how many converted users are attributed to each campaign and pick the campaign with the highest count. If there is a tie, choose the lexicographically smallest campaign name.

Additional requirements:

• Deduplicate events on (uid, ts, event_type) using a window function so that only one row per (uid, ts, event_type) is used.
• Ensure timezone consistency by treating users.created_at, orders.created_at, and events.ts uniformly as UTC when truncating to dates and months.
• Use window functions where appropriate (e.g., for deduplication and last-touch selection), and keep everything in a single query that returns one row per (cohort_month, country) with:
  • cohort_month (truncated to the first day of the month, e.g. '2025-01-01')
  • country
  • new_users
  • conversion_rate_7d
  • retention_rate_7d
  • gmv
  • top_campaign

Return cohort_month formatted as YYYY-MM-DD, conversion_rate_7d and retention_rate_7d as numeric rates, and gmv formatted with two decimal places.

Table users

column	type	constraints
uid	INT	PRIMARY KEY, NOT NULL
created_at	TIMESTAMP WITH TIME ZONE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	created_at	uid
US	2025-01-05 10:00:00+00	1
US	2025-01-20 15:00:00+00	2
CA	2025-01-25 09:00:00+00	3
US	2025-02-02 12:00:00+00	4
CA	2025-02-10 18:00:00+00	5

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
uid	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
created_at	TIMESTAMP WITH TIME ZONE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

amount	created_at	order_id	status	uid
100	2025-01-06 11:00:00+00	101	PAID	1
50	2025-01-20 12:00:00+00	102	REFUNDED	1
80	2025-01-25 09:00:00+00	103	PAID	2
60	2025-02-01 10:00:00+00	104	PAID	3
120	2025-02-05 13:00:00+00	105	CANCELED	4
200	2025-02-20 14:00:00+00	106	PAID	5

Table events

column	type	constraints
uid	INT	NOT NULL
ts	TIMESTAMP WITH TIME ZONE	NOT NULL
event_type	VARCHAR(50)	NOT NULL
campaign	VARCHAR(50)

Sample data:

campaign	event_type	ts	uid
google	page_view	2025-01-04 09:00:00+00	1
facebook	signup	2025-01-05 09:30:00+00	1
facebook	signup	2025-01-05 09:30:00+00	1
facebook	page_view	2025-01-12 10:00:00+00	1
google	page_view	2025-01-19 12:00:00+00	2
google	signup	2025-01-20 15:30:00+00	2
google	page_view	2025-01-27 09:00:00+00	2
facebook	signup	2025-01-25 08:00:00+00	3
facebook	page_view	2025-02-01 08:30:00+00	3
email	signup	2025-02-02 11:00:00+00	4
email	page_view	2025-02-09 12:00:00+00	4
email	page_view	2025-02-12 13:00:00+00	4
google	signup	2025-02-10 17:00:00+00	5
google	page_view	2025-02-20 13:30:00+00	5
google	page_view	2025-02-27 10:00:00+00	5

Expected output

One row per (cohort_month, country) with cohort-month-level new users, 7-day conversion and retention rates, GMV excluding refunded/canceled orders, and the top last-touch campaign among converted users. cohort_month is the first day of the month of users.created_at in UTC.

cohort_month	conversion_rate_7d	country	gmv	new_users	retention_rate_7d	top_campaign
2025-01-01	0	CA	0.00	1	1	NULL
2025-01-01	1	US	180.00	2	1	facebook
2025-02-01	0	CA	200.00	1	0	NULL
2025-02-01	1	US	0.00	1	1	email

Hints

1. Start by normalizing timestamps to UTC and building a cohort_month from users.created_at, then compute per-user conversion and retention flags in CTEs.
2. Use ROW_NUMBER window functions both to deduplicate events on (uid, ts, event_type) and to pick the last-touch campaign per converted user before their first order within 7 days.