select count(1) as cnt,
(select count(1) from hand_permutations) as ttl_cnt,
round(count(1)/(select count(1) from hand_permutations),4) as p
from
(select distinct card1,card2
from hand_permutations
where right(card1,1)=right(card2,1)
and (left(card1,length(card1)-1) between 'A' and 'K')
and (left(card2,length(card2)-1) between 'A' and 'K')) t1
SELECT *
FROM scores
where ((subject ='历史' and score>90)
or (subject ='地理' and score>90)
or (subject ='政治' and score>90))
and exam_date='2024-06-30'
order by score desc,student_id,subject
select *
from subject_score
where (chinese>=110 and math>=110)
or (chinese>=110 and english>=110)
or (math>=110 and english>=110)
order by student_id
select a1.*
from subject_score a1
inner join
(select student_id,
(c1+m1+e1) as tll
from
(select student_id,
case when chinese>=110 then 1 else 0 end as c1,
case when math>=110 then 1 else 0 end as m1,
case when english>=110 then 1 else 0 end as e1
from subject_score) t
) a2
on a1.student_id=a2.student_id
where a2.tll>=2
select *
from subject_score
where if(chinese>=110,1,0)+if(math>=110,1,0)+if(english>=110,1,0) >=2
select
(select count(distinct usr_id)
from user_login_log
where
((substr(login_time,12,8) between '07:30:00' and '09:30:00')
or (substr(login_time,12,8) between '18:30:00' and '20:30:00'))
and (substr(login_time,1,10) between date_add(current_date()-day(current_date())+1,interval -1 month) and date_add(last_day(current_date()),interval -1 month))) as commute,
(select count(distinct usr_id)
from user_login_log
where (substr(login_time,12,8) between '11:30:00' and '14:00:00')
and (substr(login_time,1,10) between date_add(current_date()-day(current_date())+1,interval -1 month) and date_add(last_day(current_date()),interval -1 month))) as lunch_break,
(select count(distinct usr_id)
from user_login_log
where (substr(login_time,12,8) between '22:30:00' and '01:00:00')
and (substr(login_time,1,10) between date_add(current_date()-day(current_date())+1,interval -1 month) and date_add(last_day(current_date()),interval -1 month))) as bedtime
select dt as year,
cast(avg_tmp_h as decimal(10,2)) as avg_tmp_h,
significant_change
from
(select dt,
avg_tmp_h,
lag(avg_tmp_h,1) over(),
avg_tmp_h-lag(avg_tmp_h,1) over(),
case when abs(avg_tmp_h-lag(avg_tmp_h,1) over(order by dt))>=1 then 'Yes' else 'No' end as significant_change
from
(select year(dt) as dt,
avg(replace(tmp_h,'℃','')+0) as avg_tmp_h
from weather_rcd_china
where city = 'shenzhen'
group by 1
order by 1) t1) t2
select dt as year,
round(avg_tmp_h,2) as avg_tmp_h,
significant_change
from
(select dt,
avg_tmp_h,
lag(avg_tmp_h,1) over(),
avg_tmp_h-lag(avg_tmp_h,1) over(),
case when abs(avg_tmp_h-lag(avg_tmp_h,1) over(order by dt))>=1 then 'Yes' else 'No' end as significant_change
from
(select year(dt) as dt,
avg(replace(tmp_h,'℃','')+0) as avg_tmp_h
from weather_rcd_china
where city = 'shenzhen'
group by 1
order by 1) t1) t2
select dt as year,
round(avg_tmp_h,2) as avg_tmp_h,
significant_change
from
(select dt,
avg_tmp_h,
lag(avg_tmp_h,1) over(),
avg_tmp_h-lag(avg_tmp_h,1) over(),
case when avg_tmp_h-lag(avg_tmp_h,1) over()>=1 then 'Yes' else 'No' end as significant_change
from
(select year(dt) as dt,
avg(tmp_h) as avg_tmp_h
from weather_rcd_china
where city = 'shenzhen'
group by 1
order by 1) t1) t2
WITH monthly_avg_spending AS (
SELECT
mch_nm,
DATE_FORMAT(trx_dt, '%Y-%m') AS month,
AVG(trx_amt) AS avg_spending
FROM mt_trx_rcd1
GROUP BY mch_nm, month
),
lagged_avg_spending AS (
SELECT
mas.mch_nm,
mas.month,
mas.avg_spending,
LAG(mas.avg_spending) OVER (PARTITION BY mas.mch_nm ORDER BY mas.month) AS prev_avg_spending
FROM monthly_avg_spending mas
)
SELECT
las.mch_nm,
las.month,
las.avg_spending,
ROUND(((las.avg_spending - COALESCE(las.prev_avg_spending, 0)) / NULLIF(las.prev_avg_spending, 0)) * 100, 2) AS growth_rate
FROM lagged_avg_spending las
ORDER BY las.mch_nm, las.month;
select mch_nm,
month,
avg_spending,
case when lag(avg_spending,1) over(partition by mch_nm order by month) is null
then 'null'
else round((avg_spending/
lag(avg_spending,1) over(partition by mch_nm order by month)-1)*100,2)
end as growth_rate
from
(select mch_nm,
date_format(trx_dt,'%Y-%m') as month,
avg(trx_amt) as avg_spending
from mt_trx_rcd1
group by 1,2
order by 1,2) t1
select snd_usr_id as User_ID,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)
as Total_Received_Amount,
case when sum(pkt_amt)=0 then 0
else (1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100 end as Clown_Index
from tx_red_pkt_rcd
group by snd_usr_id
having case when sum(pkt_amt)=0 then 0
else (1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100 end between 88 and 99
order by Clown_Index desc
select snd_usr_id as User_ID,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)
as Total_Received_Amount,
case when sum(pkt_amt)=0 then 0
else round((1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100,6) end as Clown_Index
from tx_red_pkt_rcd
group by snd_usr_id
having case when sum(pkt_amt)=0 then 0
else round((1-sum(case when rcv_datetime !='1900-01-01 00:00:00' then pkt_amt else 0 end)/ sum(pkt_amt))*100,6) end between 88 and 99
order by Clown_Index desc
with t2 as (select t1.snd_usr_id,
t1.rcv_usr_id,
t1.pkt_amt,
t1.snd_datetime,
a2.cty as recipient_city,
first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime) as f_time,
timestampdiff(HOUR,first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime),t1.snd_datetime) as H
from tx_red_pkt_rcd t1
inner join tx_usr_bas_info a1 on t1.snd_usr_id=a1.usr_id and a1.gdr='M'
inner join tx_usr_bas_info a2 on t1.rcv_usr_id=a2.usr_id and a2.gdr='f'
where substr(t1.snd_datetime,1,10)='2021-02-13'
order by t1.snd_usr_id,t1.snd_datetime)
select snd_usr_id,rcv_usr_id,snd_datetime,pkt_amt,recipient_city
from (select *,
count(rcv_usr_id) over(partition by snd_usr_id) as cnt
from t2
where H in (0)
)t3
where cnt>=3
with t2 as (select t1.snd_usr_id,
t1.rcv_usr_id,
t1.pkt_amt,
t1.snd_datetime,
a2.cty as recipient_city,
first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime) as f_time,
timestampdiff(HOUR,first_value(t1.snd_datetime) over(partition by t1.snd_usr_id order by t1.snd_datetime),t1.snd_datetime) as H
from tx_red_pkt_rcd t1
inner join tx_usr_bas_info a1 on t1.snd_usr_id=a1.usr_id and a1.gdr='M'
inner join tx_usr_bas_info a2 on t1.rcv_usr_id=a2.usr_id and a2.gdr='f'
where substr(t1.snd_datetime,1,10)='2021-02-13'
order by t1.snd_usr_id,t1.snd_datetime)
select snd_usr_id,rcv_usr_id,snd_datetime,pkt_amt,recipient_city
from (select *,
count(rcv_usr_id) over(partition by snd_usr_id) as cnt
from t2
where H in (0,1)
)t3
where cnt>=3
with t1 as (select substr(trx_time,1,7) as rx_month,
usr_id,
sum(trx_amt) as sum_amt ,
case when sum(trx_amt)>=0 and sum(trx_amt)<=100 then '0-100'
when sum(trx_amt)>100 and sum(trx_amt)<=1000 then '100-1k'
when sum(trx_amt)>1000 and sum(trx_amt)<=10000 then '1k-1w'
when sum(trx_amt)>10000 then '>1w'
end as amt_range
from cmb_usr_trx_rcd
group by 1,2
order by 1,2)
select distinct rx_month,
amt_range,
round(count(usr_id) over(partition by rx_month,amt_range)/count(usr_id) over(partition by rx_month)*100,2) as percentage
from t1
with t1 as (select substr(trx_time,1,7) as rx_month,
usr_id,
sum(trx_amt) as sum_amt ,
case when sum(trx_amt)>=0 and sum(trx_amt)<=100 then '0-100'
when sum(trx_amt)>100 and sum(trx_amt)<=1000 then '100-1k'
when sum(trx_amt)>1000 and sum(trx_amt)<=10000 then '1k-1w'
when sum(trx_amt)>10000 then '>1w'
end as amt_range
from cmb_usr_trx_rcd
group by 1,2
order by 1,2)
select distinct rx_month,
amt_range,
count(usr_id) over(partition by rx_month,amt_range) as cnt,
count(usr_id) over(partition by rx_month) as total,
round(count(usr_id) over(partition by rx_month,amt_range)/count(usr_id) over(partition by rx_month)*100,2) as p
from t1
select distinct t2.rx_month,
t2.amt_range,
round(t2.cnt/t3.ttl*100,2) as percentage
from
(select rx_month,
amt_range,
count(distinct usr_id) as cnt
from
(select substr(trx_time,1,7) as rx_month,
usr_id,
case
when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=0
and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<100 then '0-100'
whensum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=100
and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<1000 then '100-1k'
when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=1000
and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<10000 then '1k-1w'
when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=10000 then '>1w'
end as amt_range
from cmb_usr_trx_rcd) t1
group by 1,2
order by 1,2) t2
inner join
(select substr(trx_time,1,7) as rx_month,
count(distinct usr_id) as ttl
from cmb_usr_trx_rcd
group by substr(trx_time,1,7)) t3
on t2.rx_month=t3.rx_month
select t2.rx_month,
t2.amt_range,
round(t2.cnt/t3.ttl*100,2) as percentage
from
(select rx_month,
amt_range,
count(distinct usr_id) as cnt
from
(select substr(trx_time,1,7) as rx_month,
usr_id,
case
when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=0
and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<100 then '0-100'
whensum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=100
and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<1000 then '100-1k'
when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=1000
and sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)<10000 then '1k-1w'
when sum(trx_amt) over(partition by substr(trx_time,1,7),usr_id)>=10000 then '>1w'
end as amt_range
from cmb_usr_trx_rcd) t1
group by 1,2
order by 1,2) t2
inner join
(select substr(trx_time,1,7) as rx_month,
count(distinct usr_id) as ttl
from cmb_usr_trx_rcd
group by substr(trx_time,1,7)) t3
on t2.rx_month=t3.rx_month
select user_id
from apple_pchs_rcd
group by user_id
having count(distinct case when product_type='iPhone' then product_type end)=1
and count(distinct case when product_type<>'iPhone' then product_type end)=0
order by user_id
SELECT *
FROM scores
where ((subject ='历史' and score>90)
or (subject ='地理' and score>90)
or (subject ='政治' and score>90))
and exam_date='2024-06-30'
order by score desc,student_id,subject
SELECT *
FROM scores
where ((subject ='历史' and score>90)
or (subject ='地理' and score>90)
or (subject ='政治' and score>90))
and exam_date='2024-06-30'
order by score desc,
student_id,
subject
SELECT *
FROM scores
where ((subject in ('历史') and score>90)
or (subject in ('地理') and score>90)
or (subject in ('政治') and score>90))
and exam_date='2024-06-30'
order by score desc,
student_id,
subject
SELECT *
FROM scores
where (subject in ('历史') and score>90)
or (subject in ('地理') and score>90)
or (subject in ('政治') and score>90)
order by score desc,
student_id,
subject