with data1 as (
select distinct
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
),
data2 as (
select
T.usr_id,
T.login_date as T_date,
T_1.login_date as T_1_date
from
data1 as T
left join
data1 as T_1
on
T.usr_id = T_1.usr_id
and datediff(T.login_date, T_1.login_date) = -1
)
select
T_date as first_login_date,
concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate
from
data2
group by
T_date
order by
T_date;
SELECT
exam_date,
MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
FROM scores
WHERE student_id = 460093
GROUP BY exam_date
ORDER BY exam_date;
select year(dt) as Y
,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
from
weather_rcd_china
where
year(dt) between 2011 and 2022
group by
year(dt)
select
case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(mch_nm) rlike '按摩|保健|休闲|spa|养生|会所' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct mch_nm) as mch_cnt
from
cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
or lower(mch_nm) rlike '按摩|保健|休闲|spa|养生|会所'
group by reg_rules
order by mch_cnt desc;
select
case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(mch_nm) rlike '.按摩|保健|休闲|spa|养生|会所' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct mch_nm) as mch_cnt
from
cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;
select
case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct mch_nm) as mch_cnt
from
cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;
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(date(trx_time)) as first_date
from
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by 1
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(*) 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 1
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(1) 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 1
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 'other'
end as ser_typ,
count(trx_amt) trx_cnt,
min(trx_time) first_date
from cmb_usr_trx_rcd
group by 1
ORDER BY
CASE
WHEN ser_typ = '1.WithHand' THEN 1
WHEN ser_typ = '2.WithMimi' THEN 2
WHEN ser_typ = '3.BlowJobbie' THEN 3
WHEN ser_typ = '4.Doi' THEN 4
WHEN ser_typ = '5.DoubleFly' THEN 5
ELSE 6
END;
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'
end as ser_typ,
count(trx_amt) trx_cnt,
min(trx_time) first_date
from cmb_usr_trx_rcd
group by 1
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 '其他'
end as ser_typ,
count(trx_amt) trx_cnt,
min(trx_time) first_date
from cmb_usr_trx_rcd
group by 1
order by ser_typ
select usr_id,
mch_nm,
sum(trx_amt) trx_amt,
count(trx_amt) trx_cnt,
min(trx_time) first_time
from cmb_usr_trx_rcd
where usr_id=5201314520
and trx_amt>=288
group by usr_id,mch_nm
order by trx_cnt desc
select usr_id,
mch_nm,
sum(trx_amt) trx_amt,
count(trx_amt) trx_cnt,
min(trx_time) first_time
from cmb_usr_trx_rcd
where usr_id=5201314520
and trx_amt>=288
group by usr_id,mch_nm