本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:AI训练营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
(
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
(
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(ORDER BY W.`citric acid`) AS 'RANK', W.*
FROM `winequality-white` AS W
WHERE W.pH = 3.03;
题目三:
SELECT Merchant_id,
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_amont 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;
题目四:
SELECT SUM(DATA_VALUE) AS 'SUM', MONTH(PERIOD_DATE) AS 'MONTH' FROM `macro industry`
WHERE YEAR(PERIOD_DATE) = 2015
GROUP BY `MONTH`
ORDER BY `SUM` LIMIT 1;
题目五:
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';
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;
题目六:
SELECT RANK() OVER(ORDER BY W.`residual sugar`) AS 'RANK', ph, W.*
FROM `winequality-red` AS W
WHERE W.pH = 3.63;
基础不牢靠啊,一些题目看着要卡半天,还有一些点忘了想不到,还是靠复习和搜索才能继续做下去。