select exam_date,
max(case when subject='语文' then score else null end) as chinese_score,
max(case when subject='数学' then score else null end) as math_score,
max(case when subject='英语' then score else null end) as english_score
from scores
where student_id = 460093 and subject in ('语文','数学','英语')
group by exam_date
order by exam_date
select year(dt) as Y,
round(avg(case when city = 'beijing' then tmp_h else null end),2) as '北京',
round(avg(case when city = 'shanghai' then tmp_h else null end),2) as '上海',
round(avg(case when city = 'shenzhen' then tmp_h else null end),2) as '深圳',
round(avg(case when city = 'guangzhou' then tmp_h else null end),2) as '广州'
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
order by year(dt)
select year(dt) as Y,
round(avg(case when city = 'beijing' then tmp_h else null end),2) as '北京',
round(avg(case when city = 'shanghai' then tmp_h else null end),2) as '上海',
round(avg(case when city = 'shenzhen' then tmp_h else null end),2) as '深圳',
round(avg(case when city = 'guangzhou' then tmp_h else null end),2) as '广州'
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
select year(dt) as Y,
round(avg(case when city = 'beijing' then tmp_h else null end),2) as 北京,
round(avg(case when city = 'shanghai' then tmp_h else null end),2) as 上海,
round(avg(case when city = 'shenzhen' then tmp_h else null end),2) as 深圳,
round(avg(case when city = 'guangzhou' then tmp_h else null end),2) as 广州
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
with x as (
select prd_id,sum(if_snd) as exposure_count
from tb_pg_act_rcd
group by prd_id
order by exposure_count desc
limit 1)
select x.prd_id, prd_nm, exposure_count
from x
left join tb_prd_map
on x.prd_id = tb_prd_map.prd_id
with tt as (
select distinct usr_id, date(login_time) as login_date
from user_login_log
where date(login_time) >= date_sub(current_date, interval 180 day))
, bb as (
select usr_id, count(login_date) as days
from tt
group by usr_id)
select
count(case when days between 1 and 5 then days end) as days_1_to_5,
count(case when days between 6 and 10 then days end) as days_6_to_10,
count(case when days between 11 and 20 then days end) as days_11_to_20,
count(case when days > 20 then days end) as days_over_20
from bb
select
count(distinct
case when (date_format(login_time, '%H:%i:%s') between '07:30:00' and '09:30:00') or (date_format(login_time, '%H:%i:%s') between '18:30:00' and '20:30:00') then usr_id end) as commute,
count(distinct
case when date_format(login_time, '%H:%i:%s') between '11:30:00' and '14:00:00' then usr_id end) as lunch_break,
count(distinct
case when (date_format(login_time, '%H:%i:%s') between '22:30:00' and '23:59:59') or (date_format(login_time, '%H:%i:%s') between '00:00:00' and '01:00:00') then usr_id end) as bedtime
from user_login_log
where login_time >= date_format(date_sub(current_date, interval 1 month), '%Y-%m-01 00:00:00') and login_time <= date_format(current_date, '%Y-%m-01 00:00:00')
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select distinct snd_usr_id
from tt
where number >= 5
order by snd_usr_id
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
order by snd_usr_id
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt = 200 or pkt_amt = 520
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
with tt as (
select user_id, dayname(start_time) as day_of_week
from listen_rcd
order by user_id, dayname(start_time))
select *, count(*) as listens_per_day
from tt
group by user_id, day_of_week
order by user_id, day_of_week
with tt as (select usr_id, live_id, hour(enter_time) as enterhour
from ks_live_t1)
select
lpad(enterhour, 2, '0') as hour_entered,
count(enterhour) as enter_count
from tt
group by enterhour
order by enterhour
with tt as (select usr_id, live_id, hour(enter_time) as enterhour
from ks_live_t1)
select enterhour as hour_entered,
count(enterhour) as enter_count
from tt
group by enterhour
order by enterhour
with user_next_day as (
select *,
lead(lgdate)over(partition by usr_id order by lgdate) as nextday
from(
select distinct usr_id, date(login_time) as lgdate
from user_login_log
where datediff (current_date, date(login_time)) <= 30) as tt)
select lgdate as login_date,
concat(round(
count(distinct case when datediff(nextday, lgdate)=1 then usr_id end)/
count(distinct usr_id) * 100 , 2), '%') as T1_retention_rate
from user_next_day
group by lgdate
order by lgdate