with sale_view_15day_all as(
select open_id,mall_id from table1
where order_channel = '2'
and channel_source = 'zt_appliance'
and order_type not in ('12', '13')
and virtual_code in ('3207','3210')
and order_date>=cast(date_add('day',-15,cast('2022-01-01' as date)) as varchar)
and order_date<=cast(date_add('day',-1,cast('2022-02-01' as date)) as varchar)
-- and mall_id != '999999'
-- and mall_id != 'other'
)
,cloud_view_15_all as (
select open_id ,mall_id, pv from table2
where client_type = 'cbdqt'
and virtual_code in ('3207','3210')
and visit_date >=cast(date_add('day',-15,cast('2022-01-01' as date)) as varchar)
and visit_date <=cast(date_add('day',-1,cast('2022-02-01' as date)) as varchar)
-- and mall_id!='999999'
-- and mall_id!='other'
)
select '1' as id,count(distinct a.open_id) as activFansNum from (
select open_id,max(mall_id) as mall_id from cloud_view_15_all group by open_id,mall_id having count(pv) > 1
union
select open_id,max(mall_id) as mall_id from sale_view_15day_all group by open_id,mall_id
)a