with data1 as(
	select distinct
		usr_id,
		date(login_time) as login_time
	from 
		user_login_log 
	where 
		datediff(current_date, login_time) <= 30
	),
data2 as (
select 
	t.usr_id,
	t.login_time as t_date,
	t_1.login_time as t_1_date 
from 
	data1 as t
left join 
	data1 as t_1 
on 
	t.usr_id = t_1.usr_id
and timestampdiff(day, t.login_time, t_1.login_time) = 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

with data1 as(
	select distinct
		usr_id,
		date(login_time) as login_time
	from 
		user_login_log 
	where 
		datediff(current_date, login_time) <= 30
	),
data2 as (
select 
	t.usr_id,
	t.login_time as t_date,
	t_1.login_time as t_1_date 
from 
	data1 as t
left join 
	data1 as t_1 
on 
	t.usr_id = t_1.usr_id
and timestampdiff(day, t.login_time, t_1.login_time) = -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

with data1 as(
	select distinct
		usr_id,
		date(login_time) as login_time
	from 
		user_login_log 
	where 
		datediff(current_date, login_time) <= 30
	),
data2 as (
select 
	t.usr_id,
	t.login_time as t_date,
	t_1.login_time 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_time, t_1.login_time) = 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 
	*
from 
	didi_order_rcd
where 
	cust_uid = 'kjhd30'
order by 
	cancel_time
and finish_time = '1970-01-01 00:00:00'
limit 
	1 ;

select 
	*
from 
	didi_order_rcd
where 
	cust_uid = 'kjhd30'
order by 
	cancel_time
limit 
	1 ;

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 
	2;

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 
	2;

select count(distinct(char(mch_nm) = '%按摩保健休闲%')) as mch_cnt
from cmb_usr_trx_rcd
order by 
	1 ;

select count(distinct(char(mch_nm) = '按摩保健休闲')) as mch_cnt
from cmb_usr_trx_rcd
order by 
	1 ;

select count(char(mch_nm) = '按摩保健休闲') as mch_cnt
from cmb_usr_trx_rcd
order by 
	1 ;

select
	*
from 
	cmb_usr_trx_rcd
where
	date(trx_time)
	between '2024-09-01' and '2024-09-30'
and (
	(hour(trx_time) >= 22) 
or 
	(hour(trx_time) between 0 and 5)
)
 	and usr_id = '5201314520'
order by
	trx_time

select
	*
from 
	cmb_usr_trx_rcd
where 
	usr_id = '5201314520' and 
date(trx_time) between '2024-09-01' and '2024-09-30'
order by 	
	3 ;

select *
from cmb_usr_trx_rcd
where usr_id = '5201314520'
 AND (date(trx_time) between '2024-09-01' and '2024-09-30')
 AND hour(trx_time) between 1 and 5
ORDER BY 
	3 ;

select *
from cmb_usr_trx_rcd
where 
	(date(trx_time) between "2024-09-01" and '2024-09-30') 
and usr_id = '5201314520' 
order by 
	3 ;

select 
	subject,
AVG(score) as average_score
from 
	scores
where 
	date(exam_date) = '2024-06-30'
group by 
	subject
order by 
	2;

select 
	subject,
AVG(score) as average_score
from 
	scores
group by 
	subject
order by 
	2;

select
	class_code,
count(1) as student_count
from 
	students
group by 
	class_code
order by
	class_code ;

select 
	car_cls,
count(*) as trip_count
from
	didi_sht_rcd 
group by
	car_cls
order by 
	trip_count desc ;

select 
	city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(
cast(
sum(case when con like '%多云%' then 1 else 0 end)/ count(1) * 100 as decimal(10, 2)
)
, "%"
)
as p
from 
	weather_rcd_china
where
	year(dt) = 2021
group by 
	city
order by 
	3 desc;

select city,
	sum(case when con like "%多云%" then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like "%多云%" then 1 else 0 end)/count(1)*100 as decimal(10, 2)), "%") as 	p
from 
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	3