with a as(
select
case when pkt_amt <= 50 then '(0, 50元]'
when pkt_amt > 50 AND pkt_amt <= 200 THEN '(50元, 200元]'
else '[200元, +)' end as amount_range,
count(*) as total_sent,
sum(case when rcv_datetime ='1900-01-01 00:00:00' THEN 1 ELSE 0 END) as rejected_count
from
tx_red_pkt_rcd
where
DATE(snd_datetime) = '2021-02-13'
group by
amount_range
)
select
amount_range,
total_sent,
rejected_count,
round((rejected_count / total_sent)*100,2) as rejection_rate
from
a
select
count(*) as total_sent,
sum(case when rcv_datetime != '1900-01-01 00:00:00' then 1 else 0 end) as received_count,
round(sum(case when rcv_datetime != '1900-01-01 00:00:00' then 1 else 0 end) / count(*)*100,2) as receive_rate
from
tx_red_pkt_rcd
where
date(snd_datetime) = '2021-02-13';
SELECT
u.cty,
COUNT(r.snd_usr_id) AS send_count
FROM
tx_red_pkt_rcd r
JOIN
tx_usr_bas_info u ON r.snd_usr_id = u.usr_id
WHERE
date(r.snd_datetime) = '2021-02-13'
GROUP BY
u.cty
ORDER BY
send_count DESC
LIMIT 5;
select
r.good_id,
m.gd_nm,
count(distinct usr_id) as purchase_user_count
from
jx_pchs_rcd r
join
jx_gd_page_map m
on
r.good_id = m.gd_id
group by
r.good_id,
m.gd_nm
order by
3 desc
with a as(
select
r.cust_uid,
r.prd_id,
i.gdr
from
tb_pg_act_rcd r
join
tb_cst_bas_inf i
on
r.cust_uid = i.cust_uid
where
r.if_buy = '1'
)
select
t.prd_id,
m.prd_nm,
sum(case when t.gdr = 'M' then 1 else 0 end) as male_count,
sum(case when t.gdr = 'F' then 1 else 0 end) as female_count,
count(t.cust_uid) as total_count
from
a t
join
tb_prd_map m
on
t.prd_id = m.prd_id
group by
t.prd_id,
m.prd_nm
with a as(
select
mch_nm,
sum(trx_amt) as restaurant_revenue
from
mt_trx_rcd1
where
mch_typ2 = '面包甜点'
and
date_format(trx_dt, '%Y-%m') = '2021-03'
group by
mch_nm
),
b as(
select
sum(trx_amt) as category_revenue
from
mt_trx_rcd1
where
mch_typ2 = '面包甜点'
and
date_format(trx_dt, '%Y-%m') = '2021-03'
)
select
mch_nm,
restaurant_revenue,
category_revenue,
round((restaurant_revenue / nullif(category_revenue,0))*100,2) as market_share
from
a,b
with a as(
select
mch_nm,
cust_uid,
min(trx_dt) as first_trx_dt
from
mt_trx_rcd1
group by
mch_nm,
cust_uid
),
b as(
select
mch_nm,
date_format(first_trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as new_users
from
a
group by
mch_nm,
month
),
c as(
select
mch_nm,
date_format(trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as total_users
from
mt_trx_rcd1
group by
mch_nm,
month
)
select
b.mch_nm,
b.month,
c.total_users,
b.new_users,
round((b.new_users / c.total_users)*100,2) as new_user_ratio
from
b
join
c
on
b.mch_nm = c.mch_nm
and
b.month = c.month
group by
b.mch_nm,
b.month,
c.total_users,
b.new_users
with a as(
select
mch_nm,
cust_uid,
min(trx_dt) as first_trx_dt
from
mt_trx_rcd1
group by
mch_nm,
cust_uid
),
b as(
select
mch_nm,
date_format(first_trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as new_users
from
a
group by
mch_nm,
month
),
c as(
select
mch_nm,
date_format(trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as total_users
from
mt_trx_rcd1
group by
mch_nm,
month
)
select
b.mch_nm,
b.month,
c.total_users,
b.new_users,
(b.new_users / c.total_users)*100 as new_user_ratio
from
b
join
c
on
b.mch_nm = c.mch_nm
and
b.month = c.month
group by
b.mch_nm,
b.month,
c.total_users,
b.new_users
with a as(
select
usr_id,
min(v_date) as first_login_date
from
bilibili_t100
group by
usr_id
)
select
first_login_date,
count(*) as new_users
from
a
group by
first_login_date
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'
with a as(
select
count(order_id) as total_orders,
sum(case when grab_time != '1970-01-01 00:00:00' then 1 else 0 end)answered_orders
from
didi_order_rcd
where
date(call_time) = '2021-05-03'
)
select
total_orders,
answered_orders,
concat(format((answered_orders / total_orders)*100,2),"%") as answer_rate
from
a
select
k1.video_id,
k1.title,
round(sum((timestampdiff(second,k2.start_time,k2.end_time)))/3600,2) as total_play_duration_hours
from
ks_video_inf k1
join
ks_video_wat_log k2
on
k1.video_id = k2.video_id
where
k2.start_time >= date_sub(current_date, interval 1 month)
group by
k1.video_id,
k1.title
order by
3 desc
limit 5
select
k1.video_id,
k1.title,
round(sum((timestampdiff(second,k2.start_time,k2.end_time)))/3600,2) as total_play_duration_hours
from
ks_video_inf k1
join
ks_video_wat_log k2
on
k1.video_id = k2.video_id
where
k2.start_time >= date_sub(current_date, interval 1 month)
group by
k1.video_id,
k1.title
order by
3 desc
limit 1
select
k1.video_id,
k1.title,
COUNT(k2.uid) AS view_count,
SUM(k2.if_like) AS like_count,
SUM(k2.if_retweet) AS retweet_count,
SUM(k2.if_fav) AS fav_count,
(SUM(k2.if_like) + SUM(k2.if_retweet) + SUM(k2.if_fav)) AS total_interactions
from
ks_video_inf k1
join
ks_video_wat_log k2
on
k1.video_id = k2.video_id
where
k2.start_time >= date_sub(current_date,INTERVAL 1 MONTH)
group by
k1.video_id,
k1.title
order by
total_interactions desc
limit 3
with a1 as(
select
mch_nm,
sum(trx_amt) / count(distinct cust_uid) as avg_spending
from
mt_trx_rcd1
group by
mch_nm
)
select
mch_nm,
avg_spending,
case when avg_spending <= 100 then '低档'
when avg_spending < 300 then '中档'
else '高档'
end as label
from
a1
with a1 as(
select
cust_uid,
count(distinct mch_typ2) as mch_typ2_count
from
mt_trx_rcd1
group by
cust_uid
)
select
cust_uid,
case when mch_typ2_count >= 7 then 1 else 0 end as label
from
a1
select
t1.cty,
SUM(CASE WHEN t1.status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
SUM(CASE WHEN t1.status = 'cancel_by_usr' THEN 1 ELSE 0 END) AS cancelled_by_usr_orders,
sUM(CASE WHEN t1.status = 'cancel_by_driver' THEN 1 ELSE 0 END) AS cancelled_by_driver_orders
from
hll_t1 t1
join
hll_t2 t2
on
t1.driver_id = t2.usr_id
where
t2.role = 'driver'
group by
t1.cty
select
t1.cty,
t1.status,
count(*) as order_count
from
hll_t1 t1
join
hll_t2 t2
on
t1.driver_id = t2.usr_id
where
t2.role = 'driver'
and
t1.status in ('cancel_by_driver','completed','cancel_by_usr')
group by
t1.cty,
t1.status