Count total users' first order for each region, each day-CodePudding

I have a table called orders. Link for the table here:

I want to get the total users' first order in each region, each day. First, I tried to get: the first order for each unique user by doing this:

SELECT  customer_id, 
    MIN(order_date) first_buy, 
    region
FROM orders
GROUP BY 1
ORDER BY 2, 1;

This resulted with:

customer_id, first_buy, region
BD-11500, 2017-01-02, Central
DB-13060, 2017-01-03, West
GW-14605, 2017-01-03, West
HR-14770, 2017-01-03, West
SC-20380, 2017-01-03, West
VF-21715, 2017-01-03, Central

And so on. You can see there are 4 unique users on 2017-01-03 in West. I want to get this result:

first_buy, region, count_user
2017-01-02, Central, 1
2017-01-03, West, 4
2017-01-03, Central, 1

What do you think?

CodePudding user response：

Try this:

SELECT
first_buy = (SELECT MIN(order_date) FROM orders WHERE orders.region = ord.region),
ord.region,
count_user = ISNULL((SELECT COUNT(*) FROM orders WHERE orders.region = ord.region GROUP BY orders.customer_id), 0)
 FROM orders ord
GROUP BY ord.region

CodePudding user response：

I haven't tested this but I think this will give what you wanting to achieve

SELECT first_buy, region, COUNT(customer_id) AS count_user
FROM (SELECT  customer_id, MIN(order_date) first_buy, region
      FROM orders
      GROUP BY customer_id) AS t
GROUP BY first_buy, region