WITH a AS
(SELECT CAST(DATE_TRUNC('month', order_date) AS DATE) AS MONTH
,COUNT(DISTINCT customer_id) AS customers_this_month
FROM northwind.orders
WHERE CAST(DATE_TRUNC('month', order_date) AS DATE) BETWEEN '1996-07-01' AND '1998-05-31'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)),
b AS
(SELECT *
,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_cus
FROM northwind.orders
ORDER BY 1)
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE), COUNT(row_cus)
FROM b
WHERE row_cus = 1
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
ORDER BY 1
--SELECT CAST(DATE_TRUNC('month', order_date) AS date) AS month
-- ,SUM(COUNT(row_cus)) OVER (ORDER BY CAST(DATE_TRUNC('month', order_date) AS date)) AS total_customers
--FROM b
--GROUP BY CAST(DATE_TRUNC('month', order_date) AS date)
--SELECT a.month, a.customers_this_month, c.total_customers, --ROUND(a.customers_this_month*1.0/c.total_customers*100,2)
--FROM a
--LEFT JOIN c ON a.month=c.month
--ORDER BY a.month