Write complex SQL for cohorts and retention

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.