select usr_id,mch_nm,sum(trx_amt)trx_amt,count(usr_id) trx_cnt,min(trx_time) first_time
from cmb_usr_trx_rcd
where usr_id='5201314520'
and mch_nm in (
select distinct mch_nm from cmb_usr_trx_rcd where trx_amt>288 and usr_id='5201314520'
)
group by usr_id,mch_nm
order by count(usr_id) desc
select usr_id,mch_nm,min(trx_time),sum(trx_amt),count(usr_id)
from cmb_usr_trx_rcd
where usr_id='5201314520'
and mch_nm in (
select distinct mch_nm from cmb_usr_trx_rcd where trx_amt>288 and usr_id='5201314520'
)
group by usr_id,mch_nm
order by count(usr_id) desc
select count(distinct usr_id) incative_user_countincative_user_count from (
select year(login_time),month(login_time),usr_id,count(1)
from user_login_log
where concat(year(login_time),month(login_time))<='202407'
and usr_id not in (select usr_id
from user_login_log
where (year(login_time)='2024' and month(login_time)>'7')
or year(login_time)>'2024'
group by year(login_time),month(login_time),usr_id
having count(1)>=10
)
group by year(login_time),month(login_time),usr_id
having count(1)>=10
)t
select count(distinct usr_id) from (
select year(login_time),month(login_time),usr_id,count(1)
from user_login_log
where concat(year(login_time),month(login_time))<='202407'
and usr_id not in (select usr_id
from user_login_log
where (year(login_time)='2024' and month(login_time)>'7')
or year(login_time)>'2024'
group by year(login_time),month(login_time),usr_id
having count(1)>10
)
group by year(login_time),month(login_time),usr_id
having count(1)>10
)t
select year(login_time),month(login_time),usr_id,count(1)
from user_login_log
where year(login_time)='2024' and month(login_time)='7'
and usr_id not in (select usr_id
from user_login_log
where (year(login_time)='2024' and month(login_time)>'7')
or year(login_time)>'2024'
group by year(login_time),month(login_time),usr_id
having count(1)>10
)
group by year(login_time),month(login_time),usr_id
having count(1)>10
select count(distinct usr_id) as inactive_user_count from user_login_log where cast(login_time as date)<=date'2024-10-01'
and usr_id not in (select distinct usr_id from user_login_log where cast(login_time as date)>date'2024-10-01')
select
count(distinct usr_id) as inactive_user_count
from
user_login_log
where
date(login_time) <= '2024-10-01'
and not exists (
select 1
from user_login_log as log2
where log2.usr_id = user_login_log.usr_id
and date(log2.login_time) > '2024-10-01'
);
select count(1) as inactive_user_count
from(
select usr_id, max(login_time)
from user_login_log
group by 1
having max(login_time) <= '2024-10-01'
) tmp
select count(distinct usr_id) as inactive_user_count from user_login_log where login_time<=date'2024-10-01'
and usr_id not in (select distinct usr_id from user_login_log where login_time >date'2024-10-01')
select count(distinct usr_id) from user_login_log where login_time<=date'2024-10-01'
and usr_id not in (select usr_id from user_login_log where login_time >date'2024-10-01')
SELECT
gd.gd_id,
gd.gd_nm,
COUNT(fav.fav_trq) AS fav_count
FROM
xhs_fav_rcd fav
JOIN
gd_inf gd ON fav.mch_id = gd.gd_id
GROUP BY
gd.gd_id, gd.gd_nm
ORDER BY
fav_count DESC
LIMIT 1;