New customers per month bigquery-CodePudding

I am trying to get the amount of new customers I have per month. I'm looking for the data to be provided in something like

2019-06, 34
2019-07, 50

So far, I'm able to get the emailAddresses per month, but I'm having trouble doing this in one query. I have a couple years worth of data. How can I make a subquery for this?

SELECT emailAddress, MIN(date) AS MOD 
FROM `order-data-360921.kk.customerOrders`
GROUP BY emailAddress
HAVING MIN(date) >= '2019-06-01' AND MIN(date) < '2019-07-01';

I also tried this, which gives me errors on the count.

SELECT  EXTRACT(ISOYEAR from FirstOrderDate) AS yr, EXTRACT(MONTH from FirstOrderDate) AS month FROM `order-data-360921.kk.customerOrders`, count(*)
 SELECT * from (select emailAddress, min(date) as FirstOrderDate
      from `order-data-360921.kk.customerOrders`
      group by emailAddress
     ) oc
     group by yr(FirstOrderDate), mn(FirstOrderDate);

CodePudding user response：

select  FORMAT_DATE("%Y-%m", v1.MOD) as YYYYMM,
        COUNT(*) as countNewUsers,
from    (
          SELECT  emailAddress,
                  MIN(date) AS MOD
          FROM  `order-data-360921.kk.customerOrders`
          GROUP BY emailAddress
        ) v1
group by 1
order by 1;