with basic as (select year(list_date) as dt,
case when ts_code like '%SZ' then 1 else 0 end as co 
from stock_info
where industry = '银行')
select dt,sum(co) as cnt
from basic
group by dt
having cnt >0
order by dt

with basic as (select year(list_date) as dt,
case when ts_code like '%SZ' then 1 else 0 end as co 
from stock_info
where industry = '银行')
select dt,sum(co) as cnt
from basic
group by dt
order by dt

with basic as (select year(list_date) as dt,
case when ts_code like '%SZ' then 1 else 0 end as co 
from stock_info
where industry = '银行')
select dt,count(co) as cnt
from basic
group by dt
order by dt

with basic as (select year(list_date) as dt,
case when ts_code like '%SZ' then 1 else 0 end as co 
from stock_info)
select dt,sum(co) as cnt
from basic
group by dt
order by dt

with t as (select cust_uid,start_loc,l1.loc_ctg as locs,end_loc,l2.loc_ctg as loce
from didi_sht_rcd as dd 
join loc_nm_ctgas l1 
on dd.start_loc = l1.loc_nm
join loc_nm_ctg as l2 
on dd.end_loc = l2.loc_nm
where l1.loc_ctg = "酒店" or l2.loc_ctg = "酒店"),
ranked as (select start_loc,locs,end_loc,loce,count(*) as trip,
row_number()over(partition by locs,loce order by count(*) desc) as t
from t 
group by start_loc,locs,end_loc,loce
order by trip desc)
select start_loc,end_loc,locs,loce,trip
from ranked
where t = 1

WITH hotel_routes AS (
    SELECT r.start_loc, r.end_loc, l_start.loc_ctg AS start_ctg, l_end.loc_ctg AS end_ctg, COUNT(*) AS trip_count
    FROM didi_sht_rcd r
    JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
    JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
    WHERE l_start.loc_ctg = '酒店' OR l_end.loc_ctg = '酒店'
    GROUP BY r.start_loc, r.end_loc, l_start.loc_ctg, l_end.loc_ctg
),
ranked_routes AS (
    SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count,
           ROW_NUMBER() OVER (PARTITION BY start_ctg, end_ctg ORDER BY trip_count DESC) AS route_rank
    FROM hotel_routes
)
SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count
FROM ranked_routes
WHERE route_rank = 1
ORDER BY trip_count DESC;

with t as (
select 
cust_uid,
start_loc,
l1.loc_ctg as locs,
end_loc,
l2.loc_ctg as loce 
from didi_sht_rcd as dd 
join loc_nm_ctg as l1 
on dd.start_loc = l1.loc_nm
join loc_nm_ctg as l2 
on dd.end_loc = l2.loc_nm
where l1.loc_ctg = '酒店' or l2.loc_ctg = '酒店'
),
ranked as (
select 
start_loc,
locs,
end_loc,
loce,
count(*) as trip,
row_number() over(partition by locs, loce order by count(*) desc) as ranking
from t 
group by start_loc, locs, end_loc, loce
)
select 
start_loc,
locs,
end_loc,
loce,
trip
from ranked
where ranking = 1
order by trip desc;

with t as (select cust_uid,start_loc,l1.loc_ctg as locs,end_loc,l2.loc_ctg as loce
from didi_sht_rcd as dd 
join loc_nm_ctgas l1 
on dd.start_loc = l1.loc_nm
join loc_nm_ctg as l2 
on dd.end_loc = l2.loc_nm
where l1.loc_ctg = "酒店" or l2.loc_ctg = "酒店"),
ranked as (select start_loc,locs,end_loc,loce,count(*) as trip,
row_number()over(partition by locs,loce order by count(*) desc) as t
from t 
group by start_loc,locs,end_loc,loce)
select start_loc,locs,end_loc,loce,trip
from ranked
where t = 1
order by trip desc

with t as (select cust_uid,start_loc,l1.loc_ctg as locs,end_loc,l2.loc_ctg as loce
from didi_sht_rcd as dd 
join loc_nm_ctgas l1 
on dd.start_loc = l1.loc_nm
join loc_nm_ctg as l2 
on dd.end_loc = l2.loc_nm
where l1.loc_ctg = "酒店" or l2.loc_ctg = "酒店"),
ranked as (select start_loc,locs,end_loc,loce,count(*) as trip,
row_number()over(partition by locs,loce order by count(*) desc) as t
from t 
group by start_loc,locs,end_loc,loce)
select start_loc,locs,end_loc,loce,trip
from ranked
where t = 1

with month_sequence as (
    select 
        date_format(dv.date_value, '%Y-%m') as trx_mon
    from 
        date_table dv
    where 
        year(dv.date_value) = 2023
        and month(dv.date_value) between 1 and 12
    group by date_format(dv.date_value, '%Y-%m')
),
monthly_trx as (
    select 
        date_format(t.trx_time, '%Y-%m') as trx_mon, 
        sum(t.trx_amt) as monthly_trx_amt
    from 
        cmb_usr_trx_rcd t
    join 
        cmb_mch_typ m on t.mch_nm = m.mch_nm
    where 
        t.usr_id = 5201314520 
        and m.mch_typ = '休闲娱乐'
        and year(t.trx_time) = 2023
    group by 
        trx_mon
),
combined_trx as (
    select 
        ms.trx_mon, 
        coalesce(mt.monthly_trx_amt, 0) as monthly_trx_amt
    from 
        month_sequence ms
    left join 
        monthly_trx mt on ms.trx_mon = mt.trx_mon
)
    select 
        trx_mon, 
        sum(monthly_trx_amt) over (order by trx_mon) as cumulative_trx_amt
    from 
        combined_trx

with tm as (
select date_format(date_value,'%Y-%m') as dt
from date_table
where year(date_value) = 2023
group by date_format(date_value,'%Y-%m')
order by dt
),
bs as (
select 
date_format(trx_time,'%Y-%m') as dt,
usr_id,
mch_nm,
coalesce(trx_amt,0) as trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
),
diedai as (
select 
tm.dt,
sum(coalesce(trx_amt,0)) as t
from tm
left join bs on tm.dt = bs.dt
left join cmb_mch_typ as typ 
on bs.mch_nm = typ.mch_nm 
and typ.mch_typ = '休闲娱乐'
group by tm.dt
)
select 
dt,
sum(t) over(order by dt) as trx_amt
from diedai
order by dt;

with tm as (select date_format(date_value,'%Y-%m') as dt
from date_table
where year(date_value) = 2023
group by date_format(date_value,'%Y-%m')
order by dt),
bs as (select date_format(trx_time,'%Y-%m') as dt,usr_id,mch_nm,coalesce(trx_amt,0) as trx_amt
from cmb_usr_trx_rcd),
diedai as (select tm.dt,sum(coalesce(trx_amt,0)) as t
from tm
left join bs
on tm.dt = bs.dt
left join cmb_mch_typ as typ
on bs.mch_nm = typ.mch_nm
where mch_typ = '休闲娱乐' and usr_id = '5201314520'
group by tm.dt)
select dt,sum(t) over(order by dt) as trx_amt
from diedai
order by dt

with tm as (select date_format(date_value,'%Y-%m') as dt
from date_table
where year(date_value) = 2023
group by date_format(date_value,'%Y-%m')
order by dt),
bs as (select date_format(trx_time,'%Y-%m') as dt,usr_id,mch_nm,trx_amt
from cmb_usr_trx_rcd),
diedai as (select tm.dt,sum(trx_amt) as t
from tm
left join bs
on tm.dt = bs.dt
left join cmb_mch_typ as typ
on bs.mch_nm = typ.mch_nm
where mch_typ = '休闲娱乐' and usr_id = '5201314520'
group by tm.dt)
select dt,sum(t) over(order by dt) as trx_amt
from diedai
order by dt

with tm as (select date_format(date_value,'%Y-%m') as dt
from date_table
where year(date_value) = 2023
group by date_format(date_value,'%Y-%m')
order by dt),
bs as (select date_format(trx_time,'%Y-%m') as dt,usr_id,mch_nm,trx_amt
from cmb_usr_trx_rcd),
diedai as (select tm.dt,sum(trx_amt) as t
from tm
left join bs
on tm.dt = bs.dt
left join cmb_mch_typ as typ
on bs.mch_nm = typ.mch_nm
where mch_typ = '休闲娱乐' and usr_id = '5201314520'
group by tm.dt)
select dt,sum(t) over(order by dt) as trx_amt
from diedai

with yy as (select date_format(date_value,"%Y-%m") as dt
from date_table
where year(date_value) = 2023
group by date_format(date_value,"%Y-%m")),
basic as (
select date_format(trx_time,"%Y-%m") as dt,usr_id,mch_nm,trx_amt
 from cmb_usr_trx_rcd
 )
 select yy.dt,sum(trx_amt) as trx_amt
 from yy
 left join basic on yy.dt= basic.dt
 left join cmb_mch_typ as typ on typ.mch_nm = basic.mch_nm
 where usr_id = '5201314520' and typ.mch_typ = '休闲娱乐'
 group by yy.dt
 order by yy.dt

WITH LiveRoomStats AS (
    SELECT 
        t1.live_id,
        t2.live_nm,
        t2.live_type,
        COUNT(*) AS enter_cnt,
        ROW_NUMBER() OVER (PARTITION BY t2.live_type ORDER BY COUNT(*) DESC) AS rnk
    FROM 
        ks_live_t1 t1
    JOIN 
        ks_live_t2 t2
    ON 
        t1.live_id = t2.live_id
    WHERE 
        DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
    GROUP BY 
        t1.live_id, t2.live_nm, t2.live_type
)
SELECT 
    live_id,
    live_nm,
    live_type,
    enter_cnt
FROM 
    LiveRoomStats
WHERE 
    rnk <= 1
ORDER BY 
    live_id;

with lt as (select date_add(call_time,interval -3 hour) as local_t,order_id
from didi_order_rcd)
select hour(local_t),count(order_id) as cnt
from lt
group by hour(local_t)
order by count(order_id)desc

with lt as (select date_add(call_time,interval 3 hour) as local_t,order_id
from didi_order_rcd)
select hour(local_t),count(order_id) as cnt
from lt
group by hour(local_t)
order by count(order_id)desc

selectcount(1) as total_orders,
sum(case when grab_time= '1970-01-01 00:00:00' then 0 else 1 end) as answered_orders,
concat(round(sum(case when grab_time= '1970-01-01 00:00:00' then 0 else 1 end) /count(1)*100,2),'%')
from didi_order_rcd
where date(call_time)= '2021-05-03'

with y_cmb as (select mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
group by mch_nm
order by sum_trx_amt desc
 limit 3 ),
monthly_top_merchants as (
 select date_format(trx_time,'%Y-%m') as trx_mon,mch_nm,sum(trx_amt) as sum_trx_amt,
 row_number()over(partition by date_format(trx_time,'%Y-%m') order by sum(trx_amt) desc) as ranking
 from cmb_usr_trx_rcd
 where usr_id = '5201314520' and year(trx_time) = 2024
 group by   trx_mon, mch_nm),
 filter_end as (
 selecttrx_mon,mch_nm,sum_trx_amt
 from monthly_top_merchants
 where ranking<=3
 )
select 
    '2024' as trx_mon, 
    mch_nm, 
    sum_trx_amt
from 
    y_cmb 
union all
select 
    trx_mon, 
    mch_nm, 
    sum_trx_amt
from 
   filter_end
order by 
    trx_mon, 
    sum_trx_amt desc;