Task06-天池龙珠计划SQL训练营

学习产出

• 练习题一

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
	pH,
	`citric acid`,
	DENSE_RANK() OVER ( ORDER BY `citric acid` ) AS rankn 
FROM
	`winequality-red` 
WHERE
	pH = 3.03;

• 练习题三

发放优惠券总⾦额最多的商家：

SELECT
	Merchant_id,-- SUM(SUBSTRING_INDEX(`Discount_rate`,':', 1)) AS sale_amount,
	SUM(
	SUBSTRING_INDEX( `Discount_rate`, ':',- 1 )) AS discount_amount 
FROM
	ccf_offline_stage1_test_revised 
WHERE
	Date_received BETWEEN '2016-07-01' 
	AND '2016-07-31' 
GROUP BY
	Merchant_id 
ORDER BY
	discount_amount DESC 
	LIMIT 1;

发放优惠券张数最多的商家：

SELECT
	Merchant_id,
	COUNT( 1 ) AS cnt 
FROM
	ccf_offline_stage1_test_revised 
WHERE
	Date_received BETWEEN '2016-07-01' 
	AND '2016-07-31' 
GROUP BY
	Merchant_id 
ORDER BY
	cnt DESC 
	LIMIT 1;

• 练习题四

 2015年⽤电最⾼峰是发⽣在哪⽉：

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 );

• 练习题五

 2016年6⽉期间，线上总体优惠券弃⽤率为多少：

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;

• 练习题六

SELECT
	pH,
	`residual sugar`,
	RANK() OVER ( ORDER BY `residual sugar` ) AS rankn 
FROM
	`winequality-white` 
WHERE
	pH = 3.63;

• 练习题七

 计算截⽌到2018年底，市值最⼤的三个⾏业是哪些：

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;

• 练习题八

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;

• 练习题九

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;

• 练习题十

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 
	LIMIT 1;