select
year(list_date) as Y,
sum(case when industry in ('全国地产','区域地产') then 1 else 0 end) as '地产',
sum(case when industry = '软件服务' then 1 else 0 end) as '软件服务'
from
stock_info
where
year(list_date) between 2000 and 2024
group by
year(list_date)
order by
1
select
year(dt),
cast(avg(case when city = 'beijing' then tmp_h else null end) as decimal(4,2)) as '北京',
cast(avg(case when city = 'shanghai' then tmp_h else null end) as decimal(4,2)) as '上海',
cast(avg(case when city = 'shenzhen' then tmp_h else null end) as decimal(4,2)) as '深圳',
cast(avg(case when city = 'guangzhou' then tmp_h else null end) as decimal(4,2)) as '广州'
from
weather_rcd_china
where
year(dt) between 2011 and 2022
group by
year(dt)
with date1 as
(
select
date(order_time) as order_date,
goods_id,
sum(order_gmv) as total_gmv
from
order_info
where
date(order_time) >= '2024-10-01'
and
date(order_time) <= '2024-10-31'
group by
goods_id,date(order_time)
),
date2 as
(
select
order_date,
goods_id,
total_gmv,
row_number()over(partition by order_date order by total_gmv) as ranking
from
date1
)
select
order_date,
goods_id,
total_gmv,
ranking
from
date2
where
ranking <=3
group by
order_date,
goods_id
order by
order_date asc,
total_gmv asc
select substr(trx_time,1,7) as trx_mon ,last_day(max(trx_time)) as last_day
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time) in (2023, 2024)
group by substr(trx_time,1,7)
order by 1
select
b.mch_typ,
a.mch_nm,
count(a.mch_nm) as trx_cnt,
sum(a.trx_amt) as trx_amt
from
cmb_usr_trx_rcd a
left join
cmb_mch_typ b
on
a.mch_nm = b.mch_nm
where
a.usr_id = '5201314520'
and
year(trx_time)=2024
and
b.mch_typ is null
group by
b.mch_typ,
a.mch_nm
order by
3 desc
SELECT
m.mch_typ,
COUNT(u.trx_amt) AS trx_cnt,
SUM(u.trx_amt) AS trx_amt
FROM
cmb_usr_trx_rcd u
LEFT JOIN
cmb_mch_typ m ON u.mch_nm = m.mch_nm
WHERE
u.usr_id = '5201314520' and left(trx_time,4)=2024
GROUP BY
m.mch_typ
ORDER BY
2 DESC
select
a.mch_nm as asshole_tried,
a.trx_cnt,
b.mch_nm as darling_tried
from
(
select
mch_nm,
count(mch_nm) as trx_cnt
from
cmb_usr_trx_rcd
where
year(trx_time) in (2023,2024)
and
usr_id = '5201314520'
group by
mch_nm
having
count(mch_nm) >=20
)a
left join
(
select
mch_nm,
count(mch_nm) as trx_cnt
from
cmb_usr_trx_rcd
where
year(trx_time) in (2023,2024)
and
usr_id = '5211314521'
group by
mch_nm
)b
on
a.mch_nm = b.mch_nm
select
a.*
from
(
select
mch_nm
from
cmb_usr_trx_rcd
where
year( trx_time) = 2024
and
usr_id = '5201314520'
group by
mch_nm
)a
join
(
select
mch_nm
from
cmb_usr_trx_rcd
where
year( trx_time) = 2024
and
usr_id = '5211314521'
group by
mch_nm
)b
on
a.mch_nm = b.mch_nm
with date1 as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,date(login_time)) <= 30
),
date2 as (
select
T.usr_id,
T.login_date as T_date,
T1.login_date as T_1_date
from
date1 as T
left join
date1 as T1
on
T.usr_id = T1.usr_id
and
datediff(T.login_date,T1.login_date) = -1
)
select
T_date,
concat(round(avg(T_1_date is not null)*100,2),'%') as T1_retention_rate
from
date2
group by
T_date
order by
T_date
select
*
from
(
select
cust_uid
from
mt_trx_rcd1
where
cust_uid='MT10000'
groupby
cust_uid
) a
left join
(
select
cust_uid,
mch_nm
from
mt_trx_rcd1
where
mch_nm = '兰州李晓明拉面馆'
group by
cust_uid,
mch_nm
order by
cust_uid asc
) b
on a.cust_uid <> b.cust_uid
select
*
from
(
select
cust_uid
from
mt_trx_rcd1
where
cust_uid='MT10000'
order by
cust_uid
) a
left join
(
select
cust_uid,
mch_nm
from
mt_trx_rcd1
where
mch_nm = '兰州李晓明拉面馆'
group by
cust_uid,
mch_nm
order by
cust_uid desc
) b
on a.cust_uid <> b.cust_uid