Hey guys I'm having trouble solving an exercise of SQL: I can't seem to understand how can I write a query that answers the question in the title based on a purchase database. I tried selecting distinct values per month, but the same customer can make a purchase in different months, so they would end up being duplicated.

SELECT 
 EXTRACT(YEAR FROM o.order_date) AS year,
 EXTRACT(MONTH FROM o.order_date) AS month,
 COUNT(DISTINCT o.customerID) AS total_customers
FROM `store1.Orders` AS o
JOIN `store1.Customers` AS c
ON o.customerID = c.customerID
GROUP BY year, month
ORDER BY month ASC

Columns of store1.Orders:

• orderID
• order_date
• purchase_status
• revenue
• customerID

Columns of store1.Customers:

• customerID
• Name
• e-mail

Could someone help me?

CodePudding user response：

I'd approach the problem from user, not from order.

SELECT
COUNT(*) as new_customers,
EXTRACT(YEAR FROM custs.first_order_date) AS year,
EXTRACT(MONTH FROM custs.first_order_date) AS month
FROM
 (SELECT
    MIN(o.order_date) as first_order_date,
    o.customerID
    FROM store1.Orders o
    GROUP BY c.customer_ID) custs
GROUP BY year, month
ORDER BY month ASC

There may be typos or syntax errors in the query, I didn't test it. But you'll get approach.