With daily_unique_logins AS(
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
login_time>=date_sub(curdate(),interval 30 day)
group by
usr_id,
date(login_time)),
next_day_logins as(
select
dul1.usr_id,
dul1.login_date as first_login_date,
dul2.login_date as next_day_login_date
from
daily_unique_logins dul1
left join
daily_unique_logins dul2
On
dul1.usr_id=dul2.usr_id
and date(dul2.login_date)=date(date_add(dul1.login_date,interval 1 day)))
select
first_login_date,
concat(cast(100*count(distinct case when next_day_login_date is not null then usr_id end)/count(distinct usr_id ) as decimal(2,2)),'%')as T1_retention_rate
from
next_day_logins
group by
first_login_date
order by
first_login_date
select
goods_id,
sum(order_gmv) as total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from order_info
where date(order_time)= '2024-09-10'
group by goods_id
order by total_gmv desc
limit 10
select
goods_id,
sum(order_gmv) as total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from order_info
where date(order_time)= 2024-09-10
group by goods_id
order by total_gmv desc
limit 10
select
a.mch_nm as asshole_tried,
a.trx_cnt as asshole_tried_cnt,
b.mch_nm as darling_tried
from
(select mch_nm,
count(mch_nm) as trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520'
AND year(date(trx_time)) between 2023 and 2024
group by mch_nm
having count(mch_nm) >= 20
) a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id='5211314521'
AND year(date(trx_time)) between 2023 and 2024
) b
on a.mch_nm = b.mch_nm
order by asshole_tried_cnt desc;
select
a.mch_nm as asshole_tried,
a.trx_amt as asshole_tried_cnt,
b.mch_nm as darling_tried
from
(select * from cmb_usr_trx_rcd
where usr_id='5201314520'
AND year(date(trx_time)) between 2023 and 2024
and trx_amt >=20
) a
left join
(select * from
cmb_usr_trx_rcd
where usr_id='5211314521'
AND year(date(trx_time)) between 2023 and 2024
) b
on a.mch_nm=b.mch_nm
order by asshole_tried_cnt desc;
SELECT a.mch_nm
FROM
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id='5211314521') a
INNER JOIN
(SELECT DISTINCT mch_nm
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520') b
ON a.mch_nm = b.mch_nm
ORDER BY a.mch_nm;
SELECT DISTINCT a.mch_nm
FROM
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5211314521') a
INNER JOIN
(SELECT DISTINCT mch_nm
FROM cmb_usr_trx_rcd
WHERE year(trx_time)=2024 and usr_id = '5201314520') b
ON a.mch_nm = b.mch_nm
ORDER BY a.mch_nm;
SELECT DISTINCT a.mch_nm
FROM
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id='5211314521') a
INNER JOIN
(SELECT DISTINCT mch_nm
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520') b
ON a.mch_nm = b.mch_nm
ORDER BY a.mch_nm;
SELECT DISTINCT a.mch_nm
FROM cmb_usr_trx_rcd a
INNER JOIN
(SELECT DISTINCT mch_nm
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520') b
ON a.mch_nm = b.mch_nm
WHERE a.usr_id = '5211314521'
ORDER BY a.mch_nm;
select distinct a.* from
(selectmch_nm
from cmb_usr_trx_rcd
whereusr_id='5211314521')a
inner join
(selectmch_nm
from cmb_usr_trx_rcd
whereusr_id='5201314520')b
on a.mch_nm = b.mch_nm
order by mch_nm
select a.* from
(select distinct mch_nm
from cmb_usr_trx_rcd
whereyear(date(trx_time))=2024 and usr_id='5211314521')a
inner join
(select distinct mch_nm
from cmb_usr_trx_rcd
whereyear(date(trx_time))=2024 and usr_id='5201314520')b
on a.mch_nm = b.mch_nm
order by mch_nm
select a.* from
(select distinct mch_nm
from cmb_usr_trx_rcd
whereusr_id='5211314521')a
inner join
(select distinct mch_nm
from cmb_usr_trx_rcd
whereusr_id='5201314520')b
on a.mch_nm = b.mch_nm
order by mch_nm
select
*
from
cmb_usr_trx_rcd
where
trx_amt = (
select max(trx_amt)
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and year(date(trx_time)) = 2024
)
and usr_id = '5201314520'
and year(trx_time) = 2024;
SELECT
CASE trx_amt
WHEN 288 THEN '1.WithHand'
WHEN 388 THEN '2.WithMimi'
WHEN 588 THEN '3.BlowJobbie'
WHEN 888 THEN '4.Doi'
WHEN 1288 THEN '5.DoubleFly'
ELSE '6.other'
END AS ser_typ,
COUNT(trx_amt) AS trx_cnt,
MIN(date(trx_time)) AS first_date
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY ser_typ
ORDER BY ser_typ;
SELECT
CASE
WHEN trx_amt = 288 THEN '1.WithHand'
WHEN trx_amt = 388 THEN '2.WithMimi'
WHEN trx_amt = 588 THEN '3.BlowJobbie'
WHEN trx_amt = 888 THEN '4.Doi'
WHEN trx_amt = 1288 THEN '5.DoubleFly'
ELSE '6.other'
END AS ser_typ,
COUNT(trx_amt) AS trx_cnt,
MIN(date(trx_time)) AS first_date
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY ser_typ
ORDER BY ser_typ;
SELECT
CASE
WHEN trx_amt = 288 THEN '1.WithHand'
WHEN trx_amt = 388 THEN '2.WithMimi'
WHEN trx_amt = 588 THEN '3.BlowJobbie'
WHEN trx_amt = 888 THEN '4.Doi'
WHEN trx_amt = 1288 THEN '5.DoubleFly'
ELSE '6.other'
END AS ser_typ,
COUNT(trx_amt) AS trx_cnt,
MIN(trx_time) AS first_date
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY ser_typ
ORDER BY ser_typ;