Write a SQL query from plain English

WITH months AS (
    SELECT generate_series(DATE '2026-01-01', DATE '2026-12-01', INTERVAL '1 month')::date AS month_start
),
converted AS (
    SELECT u.id
    FROM users u
    WHERE EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.user_id = u.id
          AND o.status = 'paid'
          AND o.created_at >= u.created_at
          AND o.created_at <  u.created_at + INTERVAL '30 days'
    )
)
SELECT
    m.month_start,
    COUNT(u.id) AS new_users,
    COUNT(c.id) AS converted_users,
    ROUND(100.0 * COUNT(c.id) / NULLIF(COUNT(u.id), 0), 1) AS conversion_pct
FROM months m
LEFT JOIN users u
    ON u.created_at >= m.month_start
   AND u.created_at <  m.month_start + INTERVAL '1 month'
LEFT JOIN converted c
    ON c.id = u.id
GROUP BY m.month_start
ORDER BY m.month_start;

Line-by-line explanation

1. WITH months AS (: Starts a common table expression (CTE) named months.
2. generate_series(...): Produces one row per month from January to December 2026, so months with no signups still appear.
3. CTE converted AS (: Builds the set of users who qualify as converted.
4. WHERE EXISTS (...): Keeps a user if at least one matching order exists. EXISTS avoids duplicate rows when a user has several paid orders.
5. o.status = 'paid': Restricts to paid orders.
6. o.created_at >= u.created_at and < u.created_at + INTERVAL '30 days': Requires the order to fall within 30 days after signup.
7. FROM months m: Starts the main query from the full list of 2026 months.
8. LEFT JOIN users u ON ... >= m.month_start AND ... < m.month_start + 1 month: Matches each user to the month they signed up in. The LEFT JOIN keeps months with zero signups.
9. LEFT JOIN converted c ON c.id = u.id: Flags which of those users converted.
10. COUNT(u.id): Counts new users per month. Users with no match contribute NULL, so the count is 0.
11. COUNT(c.id): Counts converted users per month.