oracle 查询2021年至今仅在B2C渠道购物过的用户数

昨天业务让提个数，刚来公司也是在尽快熟悉业务数据的过程，一开始没有细想，写成了如下sql:

select count(distinct a.user_id)
from
(select user_id,PHARMACY_ID  from o2o.c_order
where CREATED_AT>=to_date('2021-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
union all 
select user_id,PHARMACY_ID from o2o.c_order_history
where CREATED_AT>=to_date('2021-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))a
left join o2o.p_pharmacy b on a.PHARMACY_ID=b.id 
where b.COOPERATE_TYPE=4

后来仔细一想，我这样只是查出了在B2C渠道购物过的用户数，这个用户数里的用户可能也在其他渠道购物过，所以又重新写了一下，针对用户在where结果集重新进行了过滤

select count(distinct a.user_id)
from
(select user_id,PHARMACY_ID from o2o.c_order
where CREATED_AT>=to_date('2021-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
union all 
select user_id,PHARMACY_ID from o2o.c_order_history
where CREATED_AT>=to_date('2021-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))a
where 
a.user_id 
not in ( 
select a.user_id
from
(select user_id,PHARMACY_ID from o2o.c_order
where CREATED_AT>=to_date('2021-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
union all 
select user_id,PHARMACY_ID from o2o.c_order_history
where CREATED_AT>=to_date('2021-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))a
left join o2o.p_pharmacy b on a.PHARMACY_ID=b.id
where  
b.COOPERATE_TYPE not in(4))