本笔记为阿里云天池龙珠SQL训练营的task06的学习内容，链接为“https://tianchi.aliyun.com/forum/postDetail?spm=5176.20222307.J_9”

task06

练习题解答如下：

练习题2

select pc, `citric acid` ,

dense_rank() over ( patiton order by citric acid) as ranking

from winequality-red

where ph = 3.03;

注 dense_rank() over ( patiton order by)为中式排序。

练习题3

select * 

from

(select Merchant_id,sum(SUBSTRING_INDEX(`Discount_rate`,':',-2)) as sale_amount

from  ccf_offline_stage1_test_revised

froup by  Merchant_id

order by  sale_amount desc

LIMIT 1

) as b

join

(select Merchant_id, count(Coupon_id) as number 

from  ccf_offline_stage1_test_revised

where Date_received >= '2016-07-01' and Date_received < '2016-08-01'

group by Merchant_id

order by number desc

LIMIT 1) as a;

练习题5

select sum(case when Date is null and Coupon_id is not null
then  1 

else 0 end) / sum(case when  Coupon_id is not null

then  1 

else  0 end) as discard_id

from  ccf_online_stage1_train 

where  Date_received between '2016-06-01' and '2016-06-30';

select Merchant_id,

sum(case when Date is null and Coupon_id is not null

then 1 

else 0 end ) / sum(case when Coupon_id is not null
then1 

else 0  end) as discard_id

from ccf_online_stage1_train

where Date_received between  '2016-06-01' and '2016-06-30'

group by Merchant_id

order by discard_id desc

LIMIT 1;

练习题6

select residual sugar ,rank() over(order by `residual sugar` ) as`rank`

from  winequality-white 

where pH = 3.63;

练习题7

select TYPE_NAME_CN,sum(MARKET_VALUE)as SUMVALUE 

from  market data 

where END_DATE < '2019-01-01'

group by TYPE_NAME_CN

order by SUMVALUE DESC

LIMIT 3；

select BaseData.TYPE_NAME_CN, BaseData.TICKER_SYMBOL

from (SELECT TYPE_NAME_CN, TICKER_SYMBOL, MARKET_VALUE,

ROW_NUMBER() over partiton by TYPE_NAME_CN order by MARKET_VALUE) as crank

from market data S)as BaseData

left join

(

select TYPE_NAME_CN,

sum(MARKET_VALUE)

from market data 

where YEAR(END_DATE) = 2018

group by TYPE_NAME_CN

order by sum(MARKET_VALUE) desc

LIMIT 3

) TOP3TYPE

on BaseData.TYPE_NAME_CN = TOP3TYPE.TYPE_NAME_CN

where crank <= 3 

and TOP3TYPE.TYPE_NAME_CN is nnot null；

练习题8

select  data.USER_id,  data.NUM

from

(select USER_id, count(Coupon_id) as NUM from  ccf_offline_stage1_train  

where YEAR(Date) = 2016 and MONTH(Date) = 6 and Coupon_id is not null

group by USER_id

union

select USER_id, count(Coupon_id) as NUM from ccf_online_stage1_train 

where YEAR(Date) = 2016 and MONTH(Date) = 6 and Coupon_id is not null

group by USER_id) as `data`

order by  data.NUM desc

LIMIT 1

练习题9

select *

from (

select TICKER_SYMBOL,

YEAR(END_DATE) Year,

QUARTER(END_DATE) QUARTER,

sum (`VALUE`) Amount

from company operating 

where INDIC_NAME_EN = 'Baiyun Airport:Passenger throughput'

group by TICKER_SYMBOL, YEAR(END_DATE), QUARTER(END_DATE)

order by SUM(`VALUE`) DESC 

LIMIT 1 

) BaseData 

left join     income statement 

(

select TICKER_SYMBOL,

YEAR(END_DATE) YEAR,

QUARTER(END_DATE) QUARTER,

sum(N_INCOME) profit

from  income statement 

group by TICKER_SYMBOL , YEAR(END_DATE), QUARTER(END_DATE) ) Income 

on BaseData.TICKER_SYMBOL = Income.TICKER_SYMBOL

and BaseData.Year = Income.YEAR

and BaseData.QUARTER = Income.QUARTER

练习题10

select Merchant_id, sum(discount_amount) discount_amount

from(

select Merchant_id, sum(SUBSTRING_INDEX(`Discount_rate`,':',-2)) as discount_amount

from ccf_offline_stage1_train

where (Coupon_id is not null and Date is not null ) and (YEAR(Date)= 2016 and MONTH(Date) = 6)

group by  Merchant_id

union all

select Merchant_id, sum(SUBSTRING_INDEX(`Discount_rate`,':',-2)) AS discount_amount

from ccf_online_stage1_train

where (Coupon_id is not null and Date is not null) and (YEAR(Date)= 2016 and MONTH(Date) = 6)

group by Merchant_id

) BaseData

group by Merchant_id

order by discount_amount desc

LIMIT 1；