select ts_code,open_price,close_price 
from daily_stock_prices 
where trade_date='2023-10-07'and close_price>=open_price*1.01
group by ts_code

select year(list_date) as Y,count(1) as cnt
from stock_info 
where area='深圳'
group by year(list_date)

select order_id,cust_uid,call_time,grab_time,cancel_time,finish_time 
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 avg_response_time_seconds
FROM 
    didi_order_rcd
WHERE 
    grab_time != '1970-01-01 00:00:00';

select t1.live_id,ks_live_t2.live_nm,t1.enter_cnt 
from 
(select live_id,count(usr_id)as enter_cnt
from ks_live_t1
where substr(enter_time,1,13)='2021-09-12 23'
 group by live_id
order by enter_cnt desc)t1
left join 
ks_live_t2 
on 
t1.live_id = ks_live_t2.live_id
limit 5;

select 
substr(enter_time,12,2)as hour_entered,
count(usr_id) as enter_count
from ks_live_t1 
group by hour_entered
order by hour_entered

SELECT 
(SELECT COUNT(*) FROM hand_permutations WHERE right(card1, 1) = right(card2, 1))/2 AS cnt,
(SELECT COUNT(*) FROM hand_permutations)/2 AS ttl_cnt,
ROUND(CAST((SELECT COUNT(*) FROM hand_permutations WHERE right(card1, 1) = right(card2, 1))/2 AS DECIMAL(10, 3)) / CAST((SELECT COUNT(*)/2 FROM hand_permutations) AS DECIMAL(10, 3)), 3) AS p

SELECT 
(SELECT COUNT(*) FROM hand_permutations)/2 AS ttl_cnt,
(SELECT COUNT(*) FROM hand_permutations WHERE right(card1, 1) = right(card2, 1))/2 AS cnt,
ROUND(CAST((SELECT COUNT(*) FROM hand_permutations WHERE right(card1, 1) = right(card2, 1))/2 AS DECIMAL(10, 3)) / CAST((SELECT COUNT(*)/2 FROM hand_permutations) AS DECIMAL(10, 3)), 3) AS p

SELECT 
(SELECT COUNT(*) FROM hand_permutations) AS ttl_cnt,
(SELECT COUNT(*) FROM hand_permutations WHERE SUBSTR(card1, 2, 1) = SUBSTR(card2, 2, 1)) AS cnt,
ROUND(CAST((SELECT COUNT(*) FROM hand_permutations WHERE SUBSTR(card1, 2, 1) = SUBSTR(card2, 2, 1)) AS DECIMAL(10, 3)) / CAST((SELECT COUNT(*) FROM hand_permutations) AS DECIMAL(10, 3)), 3) AS p

SELECT 
SUM(main.id) AS ttl_cnt,
SUM(t1.id) AS cnt,
ROUND(SUM(t1.id) / NULLIF(SUM(main.id), 0), 3) AS probability
FROM 
hand_permutations AS main
LEFT JOIN 
(SELECT id
 FROM hand_permutations
 WHERE SUBSTR(card1, 2, 1) = SUBSTR(card2, 2, 1)) AS t1
ON 
main.id = t1.id;

select id,card1,card2
from hand_permutations 
where substr(card1,1,1)=substr(card2,1,1)
order by id

select id,card1,card2
from hand_permutations
where (left(card1,1)in('A','k')) and (left(card2,1)in('A','k'))
order by id

select goods_id as oods_id,sum(order_gmv)as total_gmv
from order_info 
where date(order_time)='2024-09-10'
group by goods_id
order by total_gmv desc
limit 10;

select goods_id as oods_id,sum(order_gmv)as total_gmv
from order_info 
where date(order_time)='2024-09-10'
group by oods_id
order by total_gmv desc
limit 10;

select order_id as oods_id,sum(order_gmv)as total_gmv
from order_info 
where date(order_time)='2024-09-10'
group by oods_id
order by total_gmv desc
limit 10;

select *
from
(select mch_nm as asshole_tired, count(usr_id) as trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520'and year(trx_time)between 2023 and 2024
group by mch_nm)t1
left join
(select mch_nm as darling_tried
from cmb_usr_trx_rcd
where usr_id='5211314521'and year(trx_time)between 2023 and 2024
group by mch_nm)t2
on t1.asshole_tired=t2.darling_tried
where trx_cnt>=20
order by trx_cnt desc

select *
from
(select mch_nm as asshole_tired, count(usr_id) as trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520'and year(trx_time)between 2023 and 2024
group by mch_nm)t1
left join
(select mch_nm as darling_tried
from cmb_usr_trx_rcd
where usr_id='5211314521'and year(trx_time)between 2023 and 2024
group by mch_nm)t2
on t1.asshole_tired=t2.darling_tried
order by trx_cnt desc

SELECT
mch_nm
FROM
cmb_usr_trx_rcd
WHERE
trx_time RLIKE '2024'
GROUP BY
mch_nm
HAVING
COUNT(
DISTINCT CASE WHEN usr_id IN (5201314520, 5211314521) THEN usr_id ELSE NULL END
) = 2
ORDER BY
mch_nm DESC

select t2.* from
(select distinct mch_nm 
from cmb_usr_trx_rcd
where usr_id='5201314520'and year(trx_time)=2024)t1
inner join
(select distinct mch_nm 
from cmb_usr_trx_rcd
where usr_id='5211314521'and year(trx_time)=2024)t2
on t1.mch_nm=t2.mch_nm
order by 1 desc

select usr_id,min(trx_time) as first_time ,mch_nm from cmb_usr_trx_rcd
where usr_id ='5201314520'and mch_nm='红玫瑰按摩保健休闲'