For how many customers was this the first-ever merchant they ordered with? SQL question

Question

I am struggling with this sql question:

For each merchant, find how many orders and first-time orders they had. First-time orders are meant from the perspective of a customer and are the first order that a customer ever made. In order words, for how many customers was this the first-ever merchant they ordered with? Output the name of the merchant, the total number of their orders and the number of these orders that were first-time orders.

orders
id int
customer_id int
merchant_id int
order_timestamp datetime
n_items int
total_amount_earned float

merchants
id int
name varchar
category varchar
order_timestamp datetime
zipcode int

this is what i have so far:

with first_time_orders as (
  select customer_id, rank() over (partition by merchant_id order by order_timestamp) as first_order
  from orders
  group by customer_id, merchant_id
)

select *
from first_time_orders;

Answer 1

you can try this

with cter as (select , dense_rank() over (partition by customer_id order by order_timestamp ) h from doordash_orders) select * from (select merchant_id,count() gb from cter group by merchant_id) h join (select merchant_id,count(*) gbb from cter where h=1 group by merchant_id) p on h.merchant_id=p.merchant_id