select * 
from apple_pchs_rcd 
where product_price>= 3000 and payment_method='Apple Pay' and order_channel='官网'
order by order_id;

select * 
from apple_pchs_rcd 
where product_price>=3000 and payment_method='Apple Pay'
order by order_id;

select * 
from singer_info 
where type2='港台' 
order by singer_id;

select * 
from singer_info 
where type2='港台' and type3='乐队'
order by singer_id;

select * 
from listen_rcd 
where (date(start_time) between '2023-12-10' and '2023-12-31') and song_id='13'
order by start_time;

select snd_usr_id
from tx_red_pkt_rcd 
where pkt_amt in (200,520)
group by snd_usr_id
having count(pkt_amt)>=5
order by snd_usr_id;

select rcv_usr_id,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd 
where year(rcv_datetime) != 1900
group by rcv_usr_id
order by sum_trx_amt desc
limit 10;

select snd_usr_id,sum(pkt_amt) as sum_trx_amt 
from tx_red_pkt_rcd
group by snd_usr_id
order by sum_trx_amtdesc
limit 10;

select student_id
from subject_score
where chinese+math+english>=300
order by student_id;

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

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

select cust_uid,
count(distinct car_cls) as unique_car_classes
from didi_sht_rcd 
group by cust_uid
order by unique_car_classes desc;

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

select * 
from didi_sht_rcd
where start_loc='海底捞西丽店' or end_loc='海底捞西丽店'
order by start_tm;

select 
COUNT(DISTINCT case when (time(login_time ) between '07:30:00' and '09:30:00') or (time(login_time) between '18:30:00' and '20:30:00') then usr_id end) as commute,
COUNT(DISTINCT case when (time(login_time) between '11:30:00' and '14:00:00') then usr_id end)as lunch_break,
COUNT(DISTINCT case WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_idend ) as bedtime
from user_login_log
where login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');

SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

select ts_code,open_price,close_price
from daily_stock_prices
where trade_date='2023-10-09' and pct_change>1;

select year(list_date) as Y,
count(distinct ts_code) as cnt
from stock_info
where right(ts_code,2)='SZ' and industry ='银行'
group by Y
order by Y ;

select year(list_date) as Y,
count(distinct ts_code) as cnt
from stock_info
where right(ts_code,2)='SZ' and name like '%银行%'
group by Y
order by Y ;

select year(list_date) as Y,
count(distinct name) as cnt
from stock_info
where right(ts_code,2)='SZ' and name like '%银行%'
group by Y
order by Y ;