WITH count_day as (
	SELECT usr_id,COUNT(distinct DATE(login_time)) as cn
FROM user_login_log
WHERE datediff(current_date,login_time)<=180
GROUP BY usr_id
)
SELECT 
	sum(case when cn between 1 and 5 then 1 else 0 end) as day_1to5,
sum(case when cn between 6 and 10 then 1 else 0 end) as day_6to10,
sum(case when cn between 11 and 20 then 1 else 0 end) as day_11to20,
sum(case when cn >20 then 1 else 0 end) as day_20to
FROM count_day

SELECT ts_code,open_price,close_price
FROM daily_stock_prices
WHERE date(trade_date) = "2023-10-9" AND pct_change >1

SELECT 
year(list_date) as year,
count(1) as cnt
from stock_info
WHERE ts_code like "%SZ" and industry="银行"
GROUP BY year(list_date)

SELECT 
year(list_date) as year,
count(ts_code) as cnt
from stock_info
WHERE ts_code like "%SZ" and industry="银行"
GROUP BY year(list_date)

SELECT 
year(list_date) as year,
count(distinct ts_code) as cnt
from stock_info
WHERE ts_code like "%SZ" and industry="银行"
GROUP BY year(list_date)

SELECT *
from didi_order_rcd
WHERE cust_uid="kjhd30" and finish_time="1970-01-01 00:00:00"
ORDER BY order_id
LIMIT 1

select
sum(timestampdiff(second,call_time,grab_time))/count(1) AS avgs
FROM didi_order_rcd
WHERE grab_time!="1970-01-01 00:00:00"

SELECT lpad(hour_entered,2,"0") as hour_enterd,enter_count
FROM(SELECT hour(enter_time) as hour_entered,count(1) as enter_count
FROM ks_live_t1 t1
GROUP BY hour(enter_time)) AS T1
order BY hour_entered asc

select 
    sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt 
    , count(1)/2 as ttl_cnt 
    ,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p 
from hand_permutations

SELECT *
FROM hand_permutations
WHERE left(card1,1)=left(card2,1)

select *
FROM hand_permutations
WHERE right(card1,1) = right(card2,1) and
(card1 like "A%" or card2 like "A%")

select *
FROM hand_permutations
WHERE concat(card1,card2) like "A%K%" or
concat(card1,card2) like "A%2%" or
concat(card1,card2) like "2%A%"

SELECT *
from hand_permutations
WHERE concat(card1,card2) like "A%A%" or
concat(card1,card2) like "K%K%" or
concat(card1,card2) like "A%K%" or
concat(card1,card2) like "K%A%"

SELECT id,card1,card2
FROM hand_permutations
WHERE concat(card1,card2) like "A%A%" or
	concat(card1,card2) like "K%K%" or
concat(card1,card2) like "A%K%" or 
concat(card1,card2) like "k%A%"
ORDER BY id

SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id=5201314520 and year(trx_time)=2024
ORDER BY trx_amt desc
limit 1

SELECT 
CASE WHEN mch_nm rlike "按摩保健休闲"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 rlike "按摩保健休闲" or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
GROUP BY reg_rules
ORDER BY mch_cnt desc

SELECT 
CASE WHEN mch_nm rlike "按摩保健休闲"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 rlike "按摩保健休闲" or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
GROUP BY reg_rules
ORDER BY mch_cnt desc

SELECT 
CASE WHEN mch_nm rlike "按摩保健休闲"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 rlike "按摩保健休闲" or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
GROUP BY reg_rules
ORDER BY mch_cnt desc

SELECT count(distinct mch_nm)
FROM cmb_usr_trx_rcd
where mch_nm rlike "按摩保健休闲"

SELECT count(distinct mch_nm)
FROM cmb_usr_trx_rcd
where mch_nm like "%按摩保健休闲%"