New Customers per Month in Python

An interviewer asks: "How many new customers did we get each month?" A customer is new in the month of their first order, and only then. So the answer takes two steps: find one first month per customer, then count the customers per month. A WITH query names the first step, and the main query reads it like a table.

Here is the same two-step shape on another question, how many customers placed one order, two orders, and so on:

WITH per_customer AS (
    SELECT customer_id, COUNT(*) AS n
    FROM orders
    GROUP BY customer_id
)
SELECT n, COUNT(*) AS customers
FROM per_customer
GROUP BY n

Dates like 2026-03-14 are text that sorts in date order, so MIN(ordered) is a customer's earliest order date, and strftime('%Y-%m', …) keeps its year and month.

Your query must print:

first_month | new_customers
2026-03 | 2
2026-04 | 1
2026-05 | 1
(3 rows)

Your task: in query, write WITH first_orders AS ( … ) with each customer_id and the month of their first order, named first_month, one row per customer. Then select first_month and COUNT(*) named new_customers from first_orders, one group per first_month.

Assignment
Follow the Coding Tutorial to build the named query first, then the main query that counts its rows.

Hint
Inside the brackets, grouping by customer_id keeps one row per customer, so nobody is counted twice. Outside, COUNT(*) per first_month counts those rows.