select
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(select
mch_nm,
count(1) trx_cnt
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5201314520'
group by mch_nm
having count(1) >= 20) a
left join (select distinct mch_nm
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5211314521') b
on a.mch_nm = b.mch_nm
where b.mch_nm is null
order by 2 desc
select
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(select
mch_nm,
count(1) trx_cnt
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5201314520'
group by mch_nm
having count(1) >= 20) a
left join (select distinct mch_nm
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5211314521') b
on a.mch_nm = b.mch_nm
order by 2 desc
select
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(select
mch_nm,
count(1) trx_cnt
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5201314520'
group by mch_nm
having count(1) >= 20) a
right join (select distinct mch_nm
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5211314521') b
on a.mch_nm = b.mch_nm
order by 2 desc
select
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(select
mch_nm,
count(1) trx_cnt
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5201314520'
group by mch_nm
having count(1) >= 20) a
left join
(select mch_nm
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5211314521') b
on a.mch_nm = b.mch_nm
where b.mch_nm is null
order by 2 desc
select
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(select
mch_nm,
count(1) trx_cnt
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5201314520'
group by mch_nm
having count(1) >= 20) a
left join
(select mch_nm
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5211314521') b
on a.mch_nm = b.mch_nm
order by 2 desc
select
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(select distinct
mch_nm,
count(1) trx_cnt
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5201314520'
group by mch_nm
having count(1) >= 20) a
left join
(select mch_nm
from cmb_usr_trx_rcd
where
year(trx_time) in (2023, 2024)
and usr_id = '5211314521') b
on a.mch_nm = b.mch_nm
order by 2 desc
select
a.mch_nm,tired
from
(select mch_nm,
count(1) as tired
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time) in (2023,2024)
group by mch_nm
having tired >= 20 )a
left join
(select mch_nm
from cmb_usr_trx_rcd
where
usr_id = 52113145521
and year(trx_time) in (2023,2024))b
on
a.mch_nm != b.mch_nm
order by
2 desc
select
*
from
(select mch_nm,
count(1) as tired
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time) in (2023,2024)
group by mch_nm
having tired >= 20 )a
left join
(select mch_nm
from cmb_usr_trx_rcd
where
usr_id = 52113145521
and year(trx_time) in (2023,2024))b
on
a.mch_nm != b.mch_nm
order by
1 desc
select
distinct *
from
(select mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5211314521
and year(trx_time)=2024
)a
inner join
(select mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time)=2024
)b
on
a.mch_nm = b.mch_nm
order by
1 desc
select
distinct a.*
from
(select mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5211314521
and year(trx_time)=2024
)a
inner join
(select mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time)=2024
)b
on
a.mch_nm = b.mch_nm
order by
1 desc
select
a.*
from
(select distinct mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5211314521
and year(trx_time)=2024
)a
inner join
(select distinct mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time)=2024
)b
on
a.mch_nm = b.mch_nm
order by
1 desc
select
a.*
from
(select distinct mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5211314521
and year(trx_time)=2024
)a
left join
(select distinct mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time)=2024
)b
on
a.mch_nm = b.mch_nm
order by
1 desc