阿里云天池SQL训练营 TASK06 学习笔记

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

练习题1

SELECT MarketData.*,

 OperatingData.INDIC_NAME_EN,

 OperatingData.VALUE,

 IncomeStatement.N_INCOME,

 IncomeStatement.T_COGS,

 IncomeStatement.T_REVENUE

 FROM (

 SELECT TICKER_SYMBOL,

 END_DATE,

 CLOSE_PRICE

 FROM `market data`

 WHERE TICKER_SYMBOL IN ('600383','600048') ) MarketData

 LEFT JOIN -- operating data

 (SELECT TICKER_SYMBOL,

 INDIC_NAME_EN,

END_DATE,

 VALUE

 FROM `company operating`

 WHERE TICKER_SYMBOL IN ('600383','600048') ) OperatingData

 ON MarketData.TICKER_SYMBOL = OperatingData.TICKER_SYMBOL

 AND MarketData.END_DATE = OperatingData.END_DATE

 LEFT JOIN -- income statement

 (SELECT DISTINCT TICKER_SYMBOL,

 END_DATE,

 T_REVENUE,

 T_COGS,

 N_INCOME

 FROM `income statement`

 WHERE TICKER_SYMBOL IN ('600383','600048') ) IncomeStatement

ON MarketData.TICKER_SYMBOL = IncomeStatement.TICKER_SYMBOL

 AND MarketData.END_DATE = IncomeStatement.END_DATE

ORDER BY MarketData.TICKER_SYMBOL, MarketData.END_DATE

select * , dense_rank() over (partition by citric acid) as rank_

from `winequality-red`

    where pH = 3.03

    order by citric acid asc;

练习题3

select max(p.discount_sum),p.merchant_id    -- 发放金额最多的商家

from (select merchant_id, sum(substring_index(discount_rate,':',1)) as discount_sum

from ccf_offline_stage1_test_revised

        where date_received between '2016-07-01' and '2016-07-30'

        group by merchant_id) p

练习题4

SELECT PERIOD_DATE,

 MAX(DATA_VALUE) FianlValue

 FROM `macro industry`

WHERE INDIC_ID = '2020101522'

 AND YEAR(PERIOD_DATE) = 2015

GROUP BY PERIOD_DATE

ORDER BY FianlValue DESC

LIMIT 1;

-- 并且相⽐去年同期增⻓/减少了多少个百分⽐？

SELECT BaseData.*,

 (BaseData.FianlValue - YoY.FianlValue) / YoY.FianlValue YoY

 FROM (SELECT PERIOD_DATE,

 MAX(DATA_VALUE) FianlValue

 FROM `macro industry`

 WHERE INDIC_ID = '2020101522'

 AND YEAR(PERIOD_DATE) = 2015

 GROUP BY PERIOD_DATE

 ORDER BY FianlValue DESC

 LIMIT 1) BaseData

 LEFT JOIN -- YOY

 (SELECT PERIOD_DATE,

 MAX(DATA_VALUE) FianlValue

 FROM `macro industry`

 WHERE INDIC_ID = '2020101522'

 AND YEAR(PERIOD_DATE) = 2014

 GROUP BY PERIOD_DATE ) YoY

 ON YEAR(BaseData.PERIOD_DATE) = YEAR(YoY.PERIOD_DATE) + 1

 AND MONTH(BaseData.PERIOD_DATE) = MONTH(YoY.PERIOD_DATE);

练习题5

SELECT SUM(CASE WHEN Date='0000-00-00' 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_rate

FROM ccf_online_stage1_train

WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30';

-- 2016年6⽉期间，优惠券弃⽤率最⾼的商家？

SELECT Merchant_id,

 SUM(CASE WHEN Date = '0000-00-00' 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_rate

 FROM ccf_online_stage1_train

WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30'

GROUP BY Merchant_id

ORDER BY discard_rate DESC

LIMIT 1;

练习题6

SELECT pH,

 `residual sugar`,

 RANK() OVER (ORDER BY `residual sugar`) AS rankn

 FROM `winequality-white`

WHERE pH= 3.63;

练习题7

SELECT TYPE_NAME_CN,

 SUM(MARKET_VALUE)

 FROM `market data`

WHERE YEAR(END_DATE) = '2018-12-31'

GROUP BY TYPE_NAME_CN

ORDER BY SUM(MARKET_VALUE) DESC

LIMIT 3

SELECT BaseData.TYPE_NAME_CN,

 BaseData.TICKER_SYMBOL

 FROM (SELECT TYPE_NAME_CN,

 TICKER_SYMBOL,

 MARKET_VALUE,

 ROW_NUMBER() OVER(PARTITION BY TYPE_NAME_CN ORDER BY MARKET_VALUE)

CompanyRanking

 FROM `market data` ) BaseData

 LEFT JOIN

 ( SELECT TYPE_NAME_CN,

 SUM(MARKET_VALUE)

 FROM `market data`

 WHERE YEAR(END_DATE) = '2018-12-31'

 GROUP BY TYPE_NAME_CN

 ORDER BY SUM(MARKET_VALUE) DESC

 LIMIT 3 ) top3Type

 ON BaseData.TYPE_NAME_CN = top3Type.TYPE_NAME_CN

WHERE CompanyRanking <= 3

 AND top3Type.TYPE_NAME_CN IS NOT NULL

练习题8

SELECT User_id,

 SUM(couponCount) couponCount

 FROM (SELECT User_id,

 count(*) couponCount

 FROM `ccf_online_stage1_train`

 WHERE (Date != 'null' AND Coupon_id != 'null')

 AND (LEFT(DATE,4)=2016 )

 GROUP BY User_id

 UNION ALL

 SELECT User_id,

 COUNT(*) couponCount

 FROM `ccf_offline_stage1_train`

 WHERE (Date != 'null' AND Coupon_id != 'null')

 AND (LEFT(DATE,4)=2016 )

 GROUP BY User_id ) BaseData

GROUP BY User_id

ORDER BY SUM(couponCount) 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) Amount

 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`,':',-1)) AS discount_amount

 FROM `ccf_online_stage1_train`

 WHERE (Date != 'null' AND Coupon_id != 'null')

 AND (LEFT(DATE,4)=2016 )

 AND MID(DATE,5,2) = '06'

 GROUP BY Merchant_id

 UNION ALL

 SELECT Merchant_id,

 SUM(SUBSTRING_INDEX(`Discount_rate`,':',-1)) AS discount_amount

 FROM `ccf_offline_stage1_train`

 WHERE (Date != 'null' AND Coupon_id != 'null')

 AND (LEFT(DATE,4)=2016 )

 AND MID(DATE,5,2) = '06'

 GROUP BY Merchant_id ) BaseData

GROUP BY Merchant_id

ORDER BY SUM(discount_amount) DESC