Count with 2 Group by(s) without subquery/CTE

Question

-- How many customers placed orders every month?

Table: Customer

Desired Output:

My code which gives the desired ouput:

with abc as (select concat(year(order_date),'/', month(order_date)) "date",customer_id
from customer
group by 1,2
order by 1)

select date,count(*) "customers_who_ordered"
from abc
group by 1;

I don't want a subquery or a CTE query. Is there a way I can get the same output in a single query?

Answer 1

You can try the below -

select concat(year(order_date),'/', month(order_date)) "date",count(distinct customer_id)
from customer
group by concat(year(order_date),'/', month(order_date))