參考：
數(shù)分面試-SQL篇

本篇文章完全按照以上參考鏈接寫，其實可以理解為個人筆記

拼多多面試題

注:部分來源于筆試,面試部分因為都是基于其他人面經(jīng)口述記錄，所以難免有一些在格式不統(tǒng)一的地方。

1.case專題-商品訂單數(shù)據(jù)

數(shù)據(jù)表:

訂單表orders,大概字段有（order_id'訂單號,'user_id‘用戶編號’, order_pay‘訂單金額’ , order_time‘下單時間’,'商品一級類目commodity_level_I','商品二級類目commodity_level_2'）

問題1. 求最近7天內(nèi)每一個一級類目下成交總額排名前3的二級類目:
思路：

1. 先給出最近七天每一個一級類目下的二級類目的成交總額

select o.commodity_level_I, o.commodity_level_2, sum(order_pay) as total_pay
from orders as o
where datediff(now(), o.order_time) <= 7
group by o.commodity_level_I, o.commodity_level_2

2. 再對每個一級類目下的成交總額進行排名

select tmp.commodity_level_I, tmp.commodity_level_2, tmp.total_pay,
row_number() over(partition by tmp.commodity_level_I order by tmp.total_pay desc) as rank
from (select o.commodity_level_I, o.commodity_level_2, sum(order_pay) as total_pay
from orders as o
where datediff(now(), o.order_time) <= 7
group by o.commodity_level_I, o.commodity_level_2) as tmp 

3. 取排名前三的即可

select tmp1.commodity_level_I as '商品一級類目', 
tmp1.commodity_level_2 as '商品二級類目', 
tmp1.total_pay as '成交總額'
from (select tmp.commodity_level_I, tmp.commodity_level_2, tmp.total_pay,
row_number() over(partition by tmp.commodity_level_I order by tmp.total_pay desc) as rank
from (select o.commodity_level_I, o.commodity_level_2, sum(order_pay) as total_pay
from orders as o
where datediff(now(), o.order_time) <= 7
group by o.commodity_level_I, o.commodity_level_2) as tmp ) as tmp1
where tmp1.rank <=3

問題2.提取8.1-8.10每一天消費金額排名在101-195的user_id
思路：

1. 選取8.1-8.10每一天每個user的消費金額

select o.order_time, o. user_id, sum(o.order_pay) as sum_pay
from orders as o
where o.order_time between '2020-08-01' and '2020-08-10'
group by o.order_time, o. user_id

2. 給出每天的消費金額排名

select tmp.order_time, tmp. user_id, tmp.sum_pay,
row_number() over(partition by tmp.order_time order by tmp.sum_pay desc) as rank
from (select o.order_time, o. user_id, sum(o.order_pay) as sum_pay
from orders as o
where o.order_time between '2020-08-01' and '2020-08-10'
group by o.order_time, o. user_id) as tmp

3.選出排名101-195的即可

select tmp1.order_time  as '訂單日期', 
tmp1. user_id , 
tmp1.sum_pay as '消費金額'
from (select tmp.order_time, tmp. user_id, tmp.sum_pay,
row_number() over(partition by tmp.order_time order by tmp.sum_pay desc) as rank
from (select o.order_time, o. user_id, sum(o.order_pay) as sum_pay
from orders as o
where o.order_time between '2020-08-01' and '2020-08-10'
group by o.order_time, o. user_id) as tmp) as tmp1
where tmp1.rank between 101 and 195;

2.case專題-活動運營數(shù)據(jù)分析

數(shù)據(jù)表

表1——訂單表orders,大概字段有（user_id‘用戶編號’, order_pay‘訂單金額’ , order_time‘下單時間’)

表2——活動報名表act_apply,大概字段有（act_id‘活動編號’, user_id‘報名用戶’,act_time‘報名時間’）

1.活動運營數(shù)據(jù)分析-統(tǒng)計每個活動對應(yīng)所有用戶在報名后產(chǎn)生的總訂單金額，總訂單數(shù)
思路：
兩表按user_id進行連接
還有一個條件是下單的時間要大于等于報名時間
然后按照act_id進行分組統(tǒng)計

select a.act_id, sum(o.order_pay) as '總金額', count(a.act_id) as '訂單數(shù)'
from orders as o
join act_apply as a
on o.user_id = a.user_id
where o.order_time >= a.act_time
group by a.act_id

參考鏈接中使用的是left join(如果需要改我這里是right join)
這里是需要斟酌一下的

2.統(tǒng)計每個活動從開始后到當(dāng)天（考試日）平均每天產(chǎn)生的訂單數(shù)，活動開始時間定義為最早有用戶報名的時間。（涉及到時間的數(shù)據(jù)類型均為：datetime）

1. 先統(tǒng)計每個活動的最小時間即為活動開始的時間

select a.act_id, a.user_id, a.act_time,
min(a.act_time) over(partition by a.act_id) as start_date
from act_apply as a

2.與訂單表連接 總的訂單數(shù)除以時間差即可

select tmp.act_id as '活動編號'， count(*)/datediff(now(), tmp.start_date) as `平均每天產(chǎn)生的訂單數(shù)`
from (select a.act_id, a.user_id, a.act_time,
min(a.act_time) over(partition by a.act_id) as start_date
from act_apply as a) as tmp
right join orders as o
on o.user_id = tmp.user_id
where o.order_time >= tmp.act_time
-- 如果group by 沒有start_date的話 sql不允許select語句使用它的 這是聚合函數(shù)的限制
group by tmp.act_id

其實不用窗口函數(shù)也是可行的

SELECT a.act_id, COUNT(*)/ DATEDIFF(NOW() - MIN(a.act_time)) as m_order_num
FROM orders as o
LEFT JOIN act_apply as a
ON a.user_id = o.user_id
WHERE order_time >= act_time
GROUP BY a.act_id;

3.case專題-用戶行為路徑分析

表1——用戶行為表tracking_log，大概字段有（user_id‘用戶編號’,opr_id‘操作編號’,log_time‘操作時間’）

問題1.統(tǒng)計每天符合以下條件的用戶數(shù)：A操作之后是B操作，AB操作必須相鄰
思路：

1. 用窗口函數(shù)給出每天每個用戶的每次操作、對應(yīng)的前一個操作

select t.user_id, t.opr_id as opr1, t.log_time,
lead(1) over(partition by t.log_time, t.user_id order by t.log_time asc) as opr2
from tracking_log as t

2.按時間進行分組 統(tǒng)計opr1為A opr2為B的用戶數(shù)目

select tmp.log_time, count(distinct tmp.user_id) as 'A-B路徑用戶計數(shù)'
from (select t.user_id, t.opr_id as opr1, t.log_time,
lead(1) over(partition by t.log_time, t.user_id order by t.log_time asc) as opr2
from tracking_log as t) as tmp
where tmp.opr1 = 'A' and tmp.opr2 = 'B'
group by tmp.log_time

問題2.統(tǒng)計用戶行為序列為A-B-D的用戶數(shù)
其中:A-B之間可以有任何其他瀏覽記錄(如C,E等),B-D之間除了C記錄可以有任何其他瀏覽記錄(如A,E等)
思路：

1. 構(gòu)造每個用戶的瀏覽記錄

select t.user_id, group_concat(t.opr_id order by t.log_time asc) as path
from tracking_log as t
group by t.user_id

GROUP_CONCAT(DISTINCT expression
ORDER BY expression
SEPARATOR sep);
//原文出自【易百教程】，商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)請保留原文鏈接：https://www.yiibai.com/mysql/group_concat.html

2.選出滿足條件的記錄

select t.user_id, group_concat(t.opr_id order by t.log_time asc) as path
from tracking_log as t
group by t.user_id
having path REGEXP 'A[A-Z]+B[^C]+D'

MySQL 正則表達式
3.統(tǒng)計數(shù)量即可

select count(tmp.user_id) as result
from (select t.user_id, group_concat(t.opr_id order by t.log_time asc) as path
from tracking_log as t
group by t.user_id
having path REGEXP 'A[A-Z]+B[^C]+D')  as tmp

參考答案

select count(user_id) as result
from
(
    select user_id,group_concat(opr_id order by log_time) as user_behavior_path
    from tracking_log
    group by user_id
    having (user_behavior_path like '%A%B%D%') 
             and (user_behavior_path not like '%A%B%C%D%')
) t

使用模糊查詢 選出滿足 ABD順序的 再排除滿足ABCD順序的即可

4.case專題-用戶留存分析

表1——用戶登陸表user_log，大概字段有（user_id‘用戶編號’，log_date‘登陸時間’)

問題1.求每天新增用戶數(shù)，以及他們第2天、30天的留存率
思路：

1. 先選出每個用戶的最小登陸時間 則該用戶是當(dāng)天的新增用戶

select u.user_id, min(u.log_time) as first_time
from user_log as u
group by u.user_id

2.與主表連接 得到每個用戶的登陸時間及其首次登陸時間
對首次登陸時間分組 統(tǒng)計數(shù)量則為每日新增用戶數(shù)

select tmp. first_time, count(distinct tmp.user_id)  as '每天新增用戶數(shù)'
from (select u.user_id, min(u.log_time) as first_time
from user_log as u
group by u.user_id) as tmp
join user_log as u
on u.user_id = tmp.user_id
group by tmp. first_time;

3. 第二天留存的用戶通過if判斷 如果登陸時間與首次登陸時間差值為1 則返回用戶id 否則記為null 統(tǒng)計數(shù)量即可
   30天留存同理

select tmp. first_time, count(distinct tmp.user_id)  as '每天新增用戶數(shù)',
count(distinct if(datediff(tmp.log_time, tmp. first_time) = 1, tmp.user_id, null)) as '第二天留存用戶數(shù)'，
count(distinct if(datediff(tmp.log_time, tmp. first_time) = 29, tmp.user_id, null)) as '第三十天留存用戶數(shù)'
from (select u.user_id, min(u.log_time) as first_time
from user_log as u
group by u.user_id) as tmp
join user_log as u
on u.user_id = tmp.user_id
group by tmp. first_time;

4. 所求留存率只需除以新增用戶數(shù) 然后用round取兩位即可

select tmp. first_time, count(distinct tmp.user_id)  as '每天新增用戶數(shù)',
round(count(distinct if(datediff(tmp.log_time, tmp. first_time) = 1, tmp.user_id, null))/count(distinct tmp.user_id), 2) as '第二天的留存率'，
round(count(distinct if(datediff(tmp.log_time, tmp. first_time) = 29, tmp.user_id, null))/count(distinct tmp.user_id), 2) as '第三十天的留存率'
from (select u.user_id, min(u.log_time) as first_time
from user_log as u
group by u.user_id) as tmp
join user_log as u
on u.user_id = tmp.user_id
group by tmp. first_time;

用窗口函數(shù)怎么做呢
其實以上思路的1前三步用窗口函數(shù)就可以實現(xiàn)

select tmp.first_date, count(distinct tmp.user_id) as '每天新增用戶數(shù)'
from (select u.user_id, u.log_time， min(u.log_time) over(partition by u.user_id) as first_date
from user_log as u) as tmp
group by tmp.first_date;

第二天的回訪數(shù)量可以這么求

select tmp.first_date, count(distinct tmp.user_id) as '第二天留存用戶數(shù)'
from (select u.user_id, u.log_time， min(u.log_time) over(partition by u.user_id) as first_date
from user_log as u) as tmp
where datediff(tmp.log_time, tmp.first_date)= 1
group by tmp.first_date;

第三十天留存用戶數(shù)同理

select tmp.first_date, count(distinct tmp.user_id) as '第三十天留存用戶數(shù)'
from (select u.user_id, u.log_time， min(u.log_time) over(partition by u.user_id) as first_date
from user_log as u) as tmp
where datediff(tmp.log_time, tmp.first_date)= 29
group by tmp.first_date;

三表連接

select t1.first_date, t1.'每天新增用戶數(shù)',
concat(round(t2.'第二天留存用戶數(shù)'/t1.'每天新增用戶數(shù)' , 2), '%') as  '第2天回訪率',
concat(round(t2.'第二天留存用戶數(shù)'/t1.'每天新增用戶數(shù)' , 2), '%') as  '第30天回訪率'
from (select tmp.first_date, count(distinct tmp.user_id) as '每天新增用戶數(shù)'
from (select u.user_id, u.log_time， min(u.log_time) over(partition by u.user_id) as first_date
from user_log as u) as tmp
group by tmp.first_date) as t1
join (select tmp.first_date, count(distinct tmp.user_id) as '第二天留存用戶數(shù)'
from (select u.user_id, u.log_time， min(u.log_time) over(partition by u.user_id) as first_date
from user_log as u) as tmp
where datediff(tmp.log_time, tmp.first_date)= 1
group by tmp.first_date) as t2
on t1. first_date = t2.first_date
join (select tmp.first_date, count(distinct tmp.user_id) as '第三十天留存用戶數(shù)'
from (select u.user_id, u.log_time， min(u.log_time) over(partition by u.user_id) as first_date
from user_log as u) as tmp
where datediff(tmp.log_time, tmp.first_date)= 29
group by tmp.first_date) as t3
on t1. first_date = t3.first_date

問題2.找近90天,30天,7天的登錄人數(shù)
思路：

1. 求得每個用戶每次登陸距離現(xiàn)在的時間

select u.user_id, u.log_time, datediff(now(), u.log_time) as '距今登陸時間天數(shù)'
from user_log as u

2.統(tǒng)計近7天的登陸人數(shù)

select count(distinct u.user_id)
from user_log as u
where datediff(now(), u.log_time) <= 7

30天，90天的類似

如果一次查詢出則可以用如下方法

select 
count(distinct if(datediff(now(), u.log_time)<=7, u.user_id, null)) as '近7天登陸人數(shù)'，
count(distinct if(datediff(now(), u.log_time)<=30, u.user_id, null)) as '近30天登陸人數(shù)'，
count(distinct if(datediff(now(), u.log_time)<=90, u.user_id, null)) as '近90天登陸人數(shù)'
from user_log as u

問題3.求用戶近一個月平均登錄時間間隔（按天）
思路：

1. 給出近一個月每次登陸前一次的登陸情況

selec u.user_id, u.log_time, lag(u.log_time) over(partition u.user_id order by u.log_time asc) as pre_log_time
from user_log as u
where datediff(now(), u.log_time) <= 30

2. 給出用戶每次登陸的時間間隔

selec u.user_id, u.log_time - lag(u.log_time) over(partition u.user_id order by u.log_time asc) as diff
from user_log as u
where datediff(now(), u.log_time) <= 30

3. 對用戶進行分組 統(tǒng)計平均的間隔

select tmp.user_id, avg(tmp.diff) as '平均間隔'
from (selec u.user_id, u.log_time - lag(u.log_time) over(partition u.user_id order by u.log_time asc) as diff
from user_log as u
where datediff(now(), u.log_time) <= 30) as tmp
group by tmp.user_id

5.case專題-統(tǒng)計特征(中位數(shù),眾數(shù),四分位數(shù))

表1——訂單表orders，字段：店鋪id(shop_id),銷量(sale),商品id(commodity_id)

問題1.求每個店鋪銷量的中位數(shù)
這個問題其實在這里做過
Leetcode569. 員工薪水中位數(shù)（困難）
還有另一個中位數(shù)的題目
Leetcode571. 給定數(shù)字的頻率查詢中位數(shù)（困難）
都挺難的 現(xiàn)在返回來看還是有些不會的
參考這篇文章還是學(xué)到了新的方法
解法一：
常規(guī)思路
設(shè)每個店鋪銷量組成的序列長度是cnt
當(dāng)cnt是偶數(shù)時,中位數(shù)所在序號是cnt/2,cnt/2+1
當(dāng)cnt是奇數(shù)時,中位數(shù)所在序號是ceil(cnt/2)
1.先給出cnt 和每個店鋪按照銷量的排名

select o.shop_id, o.sale,
count(1) over(partition by o.shop_id) as cnt,
row_number() over(partition by o.shop_id order by o.sale asc) as sale_rank
from orders as o

2. 對于cnt為奇數(shù)或偶數(shù)選出對應(yīng)排名的記錄

select tmp.shop_id, tmp.sale
from (select o.shop_id, o.sale,
count(1) over(partition by o.shop_id) as cnt,
row_number() over(partition by o.shop_id order by o.sale asc) as sale_rank
from orders as o) as tmp
where (tmp.cnt%2=0 and sale_rank in (cnt/2, cnt/2+1)) or
(tmp.cnt%2=1 and sale_rank = ceiling(cnt/2))

3. 最后對shop_id分組 對sale取平均即可

select tmp.shop_id, avg(tmp.sale) as '中位數(shù)'
from (select o.shop_id, o.sale,
count(1) over(partition by o.shop_id) as cnt,
row_number() over(partition by o.shop_id order by o.sale asc) as sale_rank
from orders as o) as tmp
where (tmp.cnt%2=0 and sale_rank in (cnt/2, cnt/2+1)) or
(tmp.cnt%2=1 and sale_rank = ceiling(cnt/2))
group by tmp.group_id

解法二：

abs(rn - (cnt+1)/2) < 1
解釋下上面的公式:
rn是給定長度為cnt的數(shù)列的序號排序，
eg:對于1,2,3,4,5,它的中位數(shù)所在序號是3,3-(5+1)/2 = 0
對于1,2,3,4，它的中位數(shù)所在序號是2,3
2 - (4+1)/2 = -0.5
3-(4+1)/2 = 0.5
可見(cnt+1)/2是一個數(shù)列的中間位置,如果是奇數(shù)數(shù)列，這個位置剛好是中位數(shù)所在
如果是偶數(shù),abs(rn - (cnt+1)/2) < 1

代碼思路與上邊很相似 只不過改了where條件

select shop_id,avg(sale) as median
from
(
    select shop_id,sale,
             row_number() over (partition by shop_id order by sale) as rn, -- 各商品銷量在其店鋪內(nèi)的順序編號
         count(1) over (partition by shop_id) as cnt -- 各店鋪的商品記錄數(shù)
    from orders
)
where abs(rn - (cnt+1)/2) < 1 -- 順序編號在店鋪商品銷量記錄數(shù)中間的，即為中位數(shù)
group by shop_id

解法三:不用窗口函數(shù)，不排序，利用中位數(shù)定義
這個有點兒繞 感興趣的可以看一下鏈接
中位數(shù)出現(xiàn)的頻率一定大于等于大于它的數(shù)和小于它的數(shù)的絕對值之差。

問題2.求每個店鋪訂購商品的眾數(shù)
不用窗口函數(shù)的思路：

1. 對每個店鋪的商品出現(xiàn)次數(shù)進行統(tǒng)計

select o.shop_id, o.commodity_id, count(*) as order_num
from order as o
group by o.shop_id, o.commodity_id

2. 選出每個店鋪頻數(shù)最高的商品次數(shù)

select tmp.shop_id, max(order_num) as max_order_num
from (select o.shop_id, o.commodity_id, count(*) as order_num
from order as o
group by o.shop_id, o.commodity_id) as tmp
group by tmp.shop_id

3. 利用二元in選出對應(yīng)字段即可

select tmp.shop_id, tmp.commodity_id, tmp.order_num
from (select o.shop_id, o.commodity_id, count(*) as order_num
from order as o
group by o.shop_id, o.commodity_id) as tmp
where (tmp.shop_id, tmp.order_num) in (select tmp.shop_id, max(order_num) as max_order_num
from (select o.shop_id, o.commodity_id, count(*) as order_num
from order as o
group by o.shop_id, o.commodity_id) as tmp
group by tmp.shop_id)

利用窗口函數(shù)的思路：

1. 對每個店鋪的商品出現(xiàn)次數(shù)進行統(tǒng)計

select o.shop_id, o.commodity_id, count(*) as order_num
from order as o
group by o.shop_id, o.commodity_id

2.選出每個店鋪頻數(shù)最高的商品次數(shù)

select tmp.shop_id, tmp.commodity_id, tmp.order_num,
max(tmp.order_num) over(partition by tmp.shop_id) as  max_order_num
from (select o.shop_id, o.commodity_id, count(*) as order_num
from order as o
group by o.shop_id, o.commodity_id) as tmp

3. 最后選出頻數(shù)等于最高頻數(shù)即可

select tmp1.shop_id, tmp1.commodity_id, tmp1.order_num
from (select tmp.shop_id, tmp.commodity_id, tmp.order_num,
max(tmp.order_num) over(partition by tmp.shop_id) as  max_order_num
from (select o.shop_id, o.commodity_id, count(*) as order_num
from order as o
group by o.shop_id, o.commodity_id) as tmp) as tmp1
where tmp1.order_num = tmp1.max_order_num

問題3.求四分位數(shù)

三個表
T1：good_id,cate_id(分類)
T2:mall_id(店鋪), good_id
T3:mall_id, credit_score(信用分)
問，在不同分類中,店鋪的信用分top25%

思路：

1. 連接三表

select T1.good_id, T2.cate_id, T2.mall_id, T3.credit_score
from T1
join T2
on T1.good_id = T2.good_id
join T3 
on T3.mall_id = T2.mall_id

2. 給出不同分類下的排名 和不同分類的總條數(shù)

select tmp.cate_id, tmp.mall_id, tmp.credit_score, 
row_number() over(partition by tmp.cate_id order by tmp.credit_score desc) as cate_rank,
count(*) over(partition by tmp.cate_id) as cate_total
from (select T1.good_id, T2.cate_id, T2.mall_id, T3.credit_score
from T1
join T2
on T1.good_id = T2.good_id
join T3 
on T3.mall_id = T2.mall_id) as tmp

3. 選出排名小于等于總數(shù)*25%的即可

select tmp2.cate_id, tmp2.mall_id, tmp2.credit_score, tmp2.cate_rank
from (select tmp.cate_id, tmp.mall_id, tmp.credit_score,
row_number() over(partition by tmp.cate_id order by tmp.credit_score desc) as cate_rank,
count(*) over(partition by tmp.cate_id) as cate_total
from (select T1.good_id, T2.cate_id, T2.mall_id, T3.credit_score
from T1
join T2
on T1.good_id = T2.good_id
join T3 
on T3.mall_id = T2.mall_id) as tmp) as tmp2
where tmp2.cate_rank <= tmp2.cate_total*0.25

6.case專題-GMV周同比統(tǒng)計

表：T —— 字段:時間(sale_date),店鋪類別(cate_id),店鋪數(shù)量(mall_num),gmv
問題1.拼多多618前后一周內(nèi)各店鋪類別gmv的日均提升幅度和比例

思路：

1. 選出618前一周的gmv 和對應(yīng)7天后的gmv

select T.sale_date, T.cate_id, T.mall_num, T.gmv，
lead(gmv, 7) over(partition by T.cate_id order by T.sale_date asc) as gmv_lead_7
from T
-- 窗口函數(shù)執(zhí)行實在where之后的所以這里的范圍是這樣 而不是 0617
where T.sale_date between '2020-06-11' and '2020-06-24'

2. 選出一周前的記錄 對店鋪類別分組 統(tǒng)計gmv的日均提升幅度和比例即可

select T.cate_id, 
avg(gmv_lead_7 - gmv) as '日均提升幅度'， 
avg((gmv_lead_7 - gmv)/gmv) as '日均提升比例'
from (select T.sale_date, T.cate_id, T.mall_num, T.gmv，
lead(gmv, 7) over(partition by T.cate_id order by T.sale_date asc) as gmv_lead_7
from T
where T.sale_date between '2020-06-11' and '2020-06-24') as tmp
where tmp.sale_date between '2020-06-11' and '2020-06-17'
group by T.cate_id;

問題2.在618前一周gmv top20%，20-40%等這5類商鋪在618后一周內(nèi)gmv日均提升幅度和比例
思路：

1. 先給出618前一周的記錄 然后對類別進行分組 得到前一周每個類別的總gmv 一遍后續(xù)求比例

select T.cate_id, sum(T.gmv) as total_gmv
from T
where T.sale_date between '2020-06-11' and '2020-06-17'
group by T.cate_id

2.根據(jù)總的gmv排名 給定五個類別的分組

select tmp.cate_id,
count(T.cate_id) as total,
row_number() over(order by tmp.total_gmv desc) as rank
from (select T.cate_id, sum(T.gmv) as total_gmv
from T
where T.sale_date between '2020-06-11' and '2020-06-17'
group by T.cate_id) as tmp

3.根據(jù)rank/total的大小可以給定分組

select tmp1.cate_id,
case when tmp1.rank <= tmp1.total*0.1 then '10%'
else tmp1.rank <= tmp1.total*0.2 then '10%-20%'
else tmp1.rank <= tmp1.total*0.3 then '20%-30%'
else tmp1.rank <= tmp1.total*0.4 then '30%-40%'
else tmp1.rank <= tmp1.total*0.5then '40%-50%'
else tmp1.rank > tmp1.total*0.5 then '50%以上'
end as gmv_quantile
from (select tmp.cate_id,
count(T.cate_id) as total,
row_number() over(order by tmp.total_gmv desc) as rank
from (select T.cate_id, sum(T.gmv) as total_gmv
from T
where T.sale_date between '2020-06-11' and '2020-06-17'
group by T.cate_id) as tmp) as tmp1

4. 之后就與第一問很相似了 只不過是對于沒類店鋪又進行了一個分類 然后根據(jù)這個分類進行統(tǒng)計

select  b.gmv_quantile, 
avg(a.gmv_lead_7 - a.gmv) as '日均提升幅度'， 
avg((a.gmv_lead_7 - a.gmv)/a.gmv) as '日均提升比例' 
from (select T.sale_date, T.cate_id, T.mall_num, T.gmv，
lead(gmv, 7) over(partition by T.cate_id order by T.sale_date asc) as gmv_lead_7
from T
where T.sale_date between '2020-06-11' and '2020-06-24') as a
join (select tmp1.cate_id,
case when tmp1.rank <= tmp1.total*0.1 then '10%'
else tmp1.rank <= tmp1.total*0.2 then '10%-20%'
else tmp1.rank <= tmp1.total*0.3 then '20%-30%'
else tmp1.rank <= tmp1.total*0.4 then '30%-40%'
else tmp1.rank <= tmp1.total*0.5then '40%-50%'
else tmp1.rank > tmp1.total*0.5 then '50%以上'
end as gmv_quantile
from (select tmp.cate_id,
count(T.cate_id) as total,
row_number() over(order by tmp.total_gmv desc) as rank
from (select T.cate_id, sum(T.gmv) as total_gmv
from T
where T.sale_date between '2020-06-11' and '2020-06-17'
group by T.cate_id) as tmp) as tmp1) as b
on a.cate_id = b.cate_id
where a.sale_date between '20190611' and '20190617'
and b.gmv_quantile in ('10%','10%-20%','20%-30%','30%-40','40%-50%')
group by b.gmv_quantile

7.case專題-連續(xù)區(qū)間問題

(拼多多二面面試題，就是找到一個session的開始和結(jié)束位置，但是具體題目條件記不清楚了，這里放個類似的題目,看看這一類題目的解題思路)
Leetcode1285. 找到連續(xù)區(qū)間的開始和結(jié)束數(shù)字（中等）
思路：

1. 利用定義變量的方法 判斷當(dāng)前值與前一值是否差一 若是則分組保持不變 否組分組+1

select L.log_id,
@group:=if(L.log_id = @pre_id + 1,@group, @group+1) as group,
@pre_id:=L.log_id
from Logs as L, (select @pre_id:=null, @group:=null) as init

2. 對于每個分組 最大值為結(jié)束 最小值為起始

select min(L.log_id) as start_id, 
max(L.log_id) as end_id
from (select L.log_id,
@group:=if(L.log_id = @pre_id + 1,@group, @group+1) as group,
@pre_id:=L.log_id
from Logs as L, (select @pre_id:=null, @group:=null) as init) as tmp
group by tmp.group;

猿輔導(dǎo)面試題

8.case專題-學(xué)生成績分析

表：Enrollments
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| course_id | int |
| grade | int |
+---------------+---------+
(student_id, course_id) 是該表的主鍵。

問題1.查詢每位學(xué)生獲得的最高成績和它所對應(yīng)的科目，若科目成績并列，取 course_id 最小的一門。查詢結(jié)果需按 student_id 增序進行排序。
解法1. 利用二元in
思路：

1. 選出沒名學(xué)生的最高成績

select E.student_id, max(E.grade) as max_grade
from Enrollments as E
group by E.student_id

2. 利用二元in選出對應(yīng)的科目

select E.student_id, E.course_id, E.grade
from Enrollments as E
where (E.student_id, E.grade) in (select E.student_id, max(E.grade) as max_grade
from Enrollments as E
group by E.student_id)

3. 按照student_id和grade分組 選出最小的course_id 然后按student_id排序即可

select E.student_id, min(E.course_id) as course_id, E.grade
from Enrollments as E
where (E.student_id, E.grade) in (select E.student_id, max(E.grade) as max_grade
from Enrollments as E
group by E.student_id)
group by E.student_id, E.grade
order by E.student_id asc;

解法2. 利用窗口函數(shù)
思路:

1. 對stdent_id進行分組 對grade desc, course_id asc進行排序 得到成績排序(相同成績的課程id小的在前)

select E.student_id, E.course_id, E.grade,
row_number() over(partition by E.student_id order by grade desc, course_id asc) as rank 
from Enrollments as E

2. 選出rank為1的記錄然后按student_id 增序進行排序即可

select tmp.student_id, tmp.course_id, tmp.grade
from (select E.student_id, E.course_id, E.grade,
row_number() over(partition by E.student_id order by grade desc, course_id asc) as rank 
from Enrollments as E) as tmp
where tmp.rank = 1
order by tmp.student_id

參考鏈接的代碼：

1. 對stdent_id進行分組 對grade desc進行排序 按dense_rank() 給定序號 最高分相同的序號都為1
2. 選出序號為1 的 然后對stdent_id進行分組 對course_id asc進行排序 選出rank為1的
3. 按照student_id排序即可

select a.student_id,a.course_id,a.grade
from
(
    select student_id,course_id,grade,
           row_number()over(partition by student_id order by course_id) as course_rank
    from
    (
        select student_id,course_id,grade,
               dense_rank()over(partition by student_id order by grade desc) as grade_rank
        from Enrollments
    ) t
    where t.grade_rank = 1
) a
where a.course_rank = 1
order by a.student_id

問題2.查詢每一科目成績最高和最低分?jǐn)?shù)的學(xué)生,輸出courseid,studentid,score
思路：

1. 先查出每門科目的最高分和最低分

select course_id,max(grade) as max_grade
from Enrollments
group by course_id

select course_id,min(grade) as min_grade
from Enrollments
group by course_id

2. 利用二元in

select E.student_id, E.course_id, E.grade
from Enrollments as E
where (E.student_id, E.grade) in 
(select E.student_id, max(E.grade) as max_grade
from Enrollments as E
group by E.student_id) or 
 (E.student_id, E.grade) in
 (select E.student_id, min(E.grade) as min_grade
from Enrollments as E
group by E.student_id)

參考的鏈接
用連接分別選出最高分和最低分 然后用union進行合并

select e.course_id,e.student_id,e.grade as score
from Enrollments e left join
(
    select course_id,max(grade) as max_grade
    from Enrollments
    group by course_id
) t on e.course_id = t.course_id
where e.grade = t.max_grade
union all
select e.course_id,e.student_id,e.grade as score
from Enrollments e left join
(
    select course_id,min(grade) as min_grade
    from Enrollments
    group by course_id
) t on e.course_id = t.course_id
where e.grade = t.min_grade

9.case專題-學(xué)生做題情況分析

表t:做題日期(time),學(xué)生id(stu_id),題目id(exer_id)

統(tǒng)計10.1-10.10每天做新題的人的數(shù)量，重點在每天
思路：

1. 每名學(xué)生對于每道題目第一次做才叫新題 也就是最小的時間

select t.time, t.stu_id, t.exer_id,
min(t.time) over(partition by t.stu_id, t.exer_id) as first_time
from t
where t.time between '2020-10-01' and '2020-10-10'

2. 如果time等于first_time就是在做新題 統(tǒng)計每名學(xué)生做新題的次數(shù) 如果為10次 則說明每天都在做新題

select tmp.stu_id
from (select t.time, t.stu_id, t.exer_id,
min(t.time) over(partition by t.stu_id, t.exer_id) as first_time
from t
where t.time between '2020-10-01' and '2020-10-10') as tmp
where tmp.time = tmp.first_time
group by tmp.stu_id
having count(tmp.stu_id) = 10

3.統(tǒng)計最終滿足條件的用戶數(shù)量

select count(a.stu_id) as '10.1-10.10每天做新題的人的數(shù)量'
from (select tmp.stu_id
from (select t.time, t.stu_id, t.exer_id,
min(t.time) over(partition by t.stu_id, t.exer_id) as first_time
from t
where t.time between '2020-10-01' and '2020-10-10') as tmp
where tmp.time = tmp.first_time
group by tmp.stu_id
having count(tmp.stu_id) = 10) as a