SELECT
a.login_date,
CONCAT(ROUND(COUNT(DISTINCT b.usr_id) * 100.0 / COUNT(DISTINCT a.usr_id), 2), '%') AS T1_retention_rate
FROM
(SELECT DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
login_time >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
) a
LEFT JOIN
(SELECT DISTINCT
usr_id,
DATE(login_time) AS sub_date
FROM
user_login_log
WHERE
login_time >= DATE_SUB(CURRENT_DATE, INTERVAL 31 DAY)
) b
ON
a.usr_id = b.usr_id
AND b.sub_date = DATE_ADD(a.login_date, INTERVAL 1 DAY)
GROUP BY
a.login_date
ORDER BY
a.login_date;
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
a.mch_nmasshole_tried,
a.trx_cnt,
b.mch_nm darling_tried
from
(
select
mch_nm,
count(trx_amt) trx_cnt
from
cmb_usr_trx_rcd
where
usr_id=5201314520
and
year(trx_time) in (2023,2024)
group by mch_nm
having count(trx_amt)>=20
) a
left join
(
select
distinct mch_nm
from
cmb_usr_trx_rcd
where
usr_id=5211314521
and
year(trx_time) in (2023,2024)
) b
on
a.mch_nm=b.mch_nm
order by
trx_cnt desc;
select
distinct a.*
from
(select
mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and
year(trx_time)=2024) a
inner join
(select
mch_nm
from
cmb_usr_trx_rcd
where
usr_id = 5211314521
and
year(trx_time)=2024) b
on
a.mch_nm=b.mch_nm
order by
1 desc;
select
distinct a.mch_nm
from
cmb_usr_trx_rcd a
join
cmb_usr_trx_rcd b
on
a.mch_nm=b.mch_nm
where
a.usr_id = 5201314520
and
b.usr_id = 5211314521
and
year(a.trx_time)=2024
order by
mch_nm desc;
select
distinct a.mch_nm
from
cmb_usr_trx_rcd a
join
cmb_usr_trx_rcd b
on
a.mch_nm=b.mch_nm
where
a.usr_id = 5201314520
and
b.usr_id = 5211314521
order by
mch_nm desc;
select
trx_amt,
count(trx_amt) total_trx_cnt,
count(distinct usr_id) unique_usr_cnt,
count(trx_amt)/count(distinct usr_id) avg_trx_per_user
from
cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and
date(trx_time) between '2023-1-1' and '2024-6-30'
group by
trx_amt
order by
avg_trx_per_user desc
limit 5;
select
trx_amt,
count(*) trx_cnt
from
cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and
date(trx_time) between '2024-1-1' and '2024-7-31'
group by
trx_amt
order by trx_cnt desc
limit 5;
select
date(trx_time) trx_date,
max(trx_amt) max_trx_amt,
min(trx_amt) min_trx_amt,
avg(trx_amt) avg_trx_amt,
sum(trx_amt) total_trx_amt
from
cmb_usr_trx_rcd
where
date(trx_time) between '2024-9-1' and '2024-9-30'
and
mch_nm = '红玫瑰按摩保健休闲'
group by
date(trx_time),mch_nm
order by
date(trx_time);
select
*
from
cmb_usr_trx_rcd
where
usr_id=5201314520
and
date(trx_time) between '2024-9-1' and '2024-9-30'
and
(hour(trx_time) between 0 and 5
or
hour(trx_time) between 22 and 24)
order by
trx_time;
select
*
from
cmb_usr_trx_rcd
where
usr_id=5201314520
and
date(trx_time) between '2024-9-1' and '2024-9-30'
and
hour(trx_time) between 1 and 5
order by
trx_time;
select
*
from
cmb_usr_trx_rcd
where
usr_id=5201314520
and
date(trx_time) between '2024-9-1' and '2024-9-30'
and
hour(trx_time) between 1 and 6
order by
trx_time;
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)