select
	length(singer_name),
count(singer_name)
from 
	singer_info
group by
	length(singer_name)

select
	singer_name,
char_length(singer_name) as len
from 
	singer_info

select 
	distinct upper(singer_name) as uppered_name
from 
	singer_info

select 
	dt,
tmp_h,
tmp_l,
con
from 
	weather_rcd_china
where
	city = 'beijing'
and
	con like '%雪%'

select 
	left(name,1) as surname,
count(name) as cnt
from
	students 
group by
	surname
order by
	cnt desc
limit 5

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
	cloudy_days desc

select
	* 
from 
	hand_permutations 
where
	left(card1,1) = left(card2,1)

select
	* 
from
	hand_permutations
where
	right(card1,1) = right(card2,1)
and
	(left(card1,1) = 'A' or left(card2,1) = 'A')
order by
	id asc

select
	* 
from
	hand_permutations
where
	right(card1,1) = right(card2,1)
and
	(card1 like 'A%' or card2 like 'A%')
order by
	id asc

select
	* 
from
	hand_permutations
where
	right(card1,1) = right(card2,1)
and
	card1 like 'A%' or card2 like 'A%'
order by
	id asc

select
	* 
from
	hand_permutations
where
	right(card1,1) = right(card2,1)
and
	left(card1,1) = 'A' or left(card2,1) = 'A'
order by
	id asc

select
	* 
from
	hand_permutations
where
	right(card1,1) = right(card2,1)
and
	left(card1,1) = 'A' or left(card2,1) = 'A'

select
	* 
from
	hand_permutations
where
	right(card1,1) = right(card2,1)
and
	left(card1,1) = 'A'

select
	* 
from 
	hand_permutations
where
	left(card1,1) in ('A','K')
and
	left(card2,1) in ('A','K')

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

select 
	count(distinct mch_nm) as mch_cnt
from 
	cmb_usr_trx_rcd
where
	mch_nm like '%按摩保健休闲%'

select 
	count(distinct usr_id) as mch_cnt
from 
	cmb_usr_trx_rcd
where
	mch_nm like '%按摩保健休闲%'

select
	user_id,
dayname(start_time) as day_of_week,
count(user_id) as listens_per_day
from 
	listen_rcd
group by
	user_id,day_of_week
order by
	user_id,day_of_week

select
	*
from 
	cmb_usr_trx_rcd
where
	usr_id = 5201314520
and
	(date(trx_time) between '2024-06-08' and '2024-06-10'
or
date(trx_time) between '2024-09-15' and '2024-09-17')

select
	*
from 
	cmb_usr_trx_rcd
where
	usr_id = 5201314520
and
	date(trx_time) between '2024-06-08' and '2024-06-10'
or
date(trx_time) between '2024-09-15' and '2024-09-17'