天池龙珠计划SQL训练营_10道经典题目解答

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

1.第一题

对三个表进行join

select
	is2.TICKER_SYMBOL ,
	is2.END_DATE ,
	is2.T_REVENUE,
	is2.T_COGS,
	is2.N_INCOME,
	md.TICKER_SYMBOL,
	md.END_DATE,
	md.CLOSE_PRICE,
	co.TICKER_SYMBOL ,
	co.INDIC_NAME_EN ,
	co.END_DATE ,
	co.VALUE
from
	`income statement` is2
join `market data` md on
	(is2.TICKER_SYMBOL = md.TICKER_SYMBOL )
join `company operating` co on
	is2.TICKER_SYMBOL = co.TICKER_SYMBOL
where
	is2.TICKER_SYMBOL = 600383
	or is2.TICKER_SYMBOL = 600048

2.第二题

使用窗体函数dense_rank()排序

select
	*,dense_rank() over (order by `citric acid` ) my_rank
from
	`winequality-red` wr
where
	pH = 3.03 ;

3.第三题

select
	Coupon_id,
	count(Merchant_id) as count_merchat,
	sum(SUBSTRING_INDEX(Discount_rate, ':',-1)) as sum_rate
from
	ccf_offline_stage1_test_revised costr
where
	extract(year from Date_received)= 2016
	and extract(month from Date_received) = 7
	and Coupon_id is not null
group by
	Coupon_id
order by count_merchat desc,sum_rate desc 
limit 1

4.第4题

select
	mi1.months,
	(mi1.sum_data / mi2.sum_data -1) as rate
from
	(
	select
		extract(month from PERIOD_DATE) as months,
		sum(DATA_VALUE) as sum_data
	from
		`macro industry`
	where
		name_cn = 'Total Electricity Consumption: Primary Industry'
		and extract(year from PERIOD_DATE)= 2015
	group by
		extract(month from PERIOD_DATE)
	order by
		sum_data desc
	limit 1 ) as mi1
join (
	select
		extract(month from PERIOD_DATE) as months,
		sum(DATA_VALUE) as sum_data
	from
		`macro industry`
	where
		name_cn = 'Total Electricity Consumption: Primary Industry'
		and extract(year from PERIOD_DATE)= 2014
	group by
		extract(month from PERIOD_DATE) ) mi2 on
	mi1.months = mi2.months

第五题

总的弃用率

select
	avg(temp.rate)
from
	(
	select
		Merchant_id,
		(count(Date_received)-count(Date))/ count(Date_received) as rate
	from
		ccf_offliqine_stage1_train cost
	where
		SUBSTRING(Date_received from 1 for 6) = 201606
	group by
		Merchant_id ) as temp

求弃用率最大的商家

select
	Merchant_id,
	(count(Date_received)-count(Date))/ count(Date_received) as rate
from
	ccf_offline_stage1_train cost
where
	SUBSTRING(Date_received from 1 for 6) = 201606
group by
	Merchant_id
having 
	rate=max(rate) 

第六题

select
	*,rank() over (order by `citric acid` ) my_rank
from
	`winequality-white` ww 
where
	pH = 3.03 ;

第七题

使用子查询和join和窗口函数凑出来的，这个方法比较繁琐

select
	*
from
	(
	select
		type.TYPE_NAME_EN,
		market.TICKER_SYMBOL,
		market.mark_value ,
		row_number() over ( partition by type.TYPE_NAME_EN
	order by
		market.mark_value) as row_num
	from
		(
		select
			TYPE_NAME_EN,
			sum(MARKET_VALUE) as mark_value
		from
			`market data` md
		where
			extract(year from END_DATE)<2019
		group by
			TYPE_NAME_EN
		order by
			mark_value desc
		limit 3 )as type
	join (
		select
			TICKER_SYMBOL,
			TYPE_NAME_EN,
			sum(MARKET_VALUE) as mark_value
		from
			`market data` md
		where
			extract(year from END_DATE)<2019
		group by
			TICKER_SYMBOL,
			TYPE_NAME_EN )market on
		market.TYPE_NAME_EN = type.TYPE_NAME_EN ) as temp
where
	temp.row_num<4

第八题

select
	temp.User_id,
	count(temp.Date) as sum_date
from
	(
	select
		User_id,
		`Date`
	from
		ccf_online_stage1_train cost
	where
		extract(year from Date_received)= 2016
		and extract(month from Date_received)= 6
union all
	select
		User_id,
		`Date`
	from
		ccf_offline_stage1_train cost2
	where
		SUBSTRING(Date_received from 1 for 6) = 201606 ) as temp
group by
	temp.User_id
order by
	sum_date desc
limit 1

##第九题

##第十题

select
	temp.Merchant_id,
	sum(temp.rate) as sum_rate
from
	(
	select
		Merchant_id,
		Discount_rate,
		SUBSTRING_INDEX(Discount_rate, ':',-1) as rate
	from
		ccf_online_stage1_train cost
	where
		extract(year from Date_received)= 2016
		and extract(month from Date_received)= 6
		and `Date` is not null
		and Discount_rate <> 'fixed'
union all
	select
		Merchant_id,
		Discount_rate,
		SUBSTRING_INDEX(Discount_rate, ':',-1) as rate
	from
		ccf_offline_stage1_train cost2
	where
		SUBSTRING(Date_received from 1 for 6) = 201606
		and `Date` is not null
		and Discount_rate <> 'fixed' ) as temp
group by temp.Merchant_id
order by sum_rate desc
limit 3