SQL打卡TASK06

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

-- 习题1答案
SELECT incs.`TICKER_SYMBOL`,
       incs.`END_DATE`,
       incs.`T_REVENUE`,
       incs.`T_COGS`,
       incs.`N_INCOME`,
       mard.`TICKER_SYMBOL`,
       mard.`END_DATE`,
       mard.`CLOSE_PRICE`,
       como.`TICKER_SYMBOL`,
       como.`INDIC_NAME_EN`,
       como.`END_DATE`,
       como.`VALUE`
FROM   (SELECT *
        FROM shop.`market data` as m
        WHERE m.TICKER_SYMBOL in('600383','600048')) AS mard
LEFT OUTER JOIN (
       SELECT *
       FROM shop.`income statement` AS i
       WHERE i.TICKER_SYMBOL in('600383','600048')) AS incs
ON     mard.TICKER_SYMBOL=incs.TICKER_SYMBOL
LEFT OUTER JOIN (
       SELECT *
       FROM shop.`company operating` AS c
       WHERE c.TICKER_SYMBOL in ('600383','600048')) AS como
ON     mard.TICKER_SYMBOL=como.TICKER_SYMBOL;

-- 习题2答案
SELECT pH,`citric acid`,DENSE_RANK() OVER(ORDER BY `citric acid`) AS citric_acid_rank
FROM shop.`winequality-red`
WHERE pH ='3.03';

-- 习题3答案
-- 优惠总金额最多
SELECT Merchant_id,
    SUM(SUBSTRING_INDEX(`Discount_rate`,':',1)) AS sale_sum,
    SUM(SUBSTRING_INDEX(`Discount_rate`,':',-1)) AS dicount_sum
FROM shop.ccf_offline_stage1_test_revised
WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY Merchant_id
ORDER BY dicount_sum DESC
LIMIT 1;
-- 优惠券张数最多
SELECT Merchant_id,
       COUNT(Merchant_id) AS dicount_count
FROM shop.ccf_offline_stage1_test_revised
WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY Merchant_id
ORDER BY dicount_count DESC
LIMIT 1;

-- 习题4答案
-- 峰值在2015年8月31日
SELECT PERIOD_DATE, MAX(DATA_VALUE) as max_value
FROM shop.`macro industry`
WHERE INDIC_ID='2020101522' 
AND YEAR(PERIOD_DATE)=2015
GROUP BY PERIOD_DATE
ORDER BY max_value DESC
LIMIT 1;
-- 同比增长2.3%
SELECT max15.max_value AS value_2015,
       max14.max_value AS value_2014,
       (max15.max_value-max14.max_value)/max14.max_value AS yoy
FROM (
        SELECT PERIOD_DATE, MAX(DATA_VALUE) as max_value
        FROM shop.`macro industry`
        WHERE INDIC_ID='2020101522' 
        AND YEAR(PERIOD_DATE)=2015
        GROUP BY PERIOD_DATE
        ORDER BY max_value DESC
        LIMIT 1) AS max15
LEFT JOIN (
        SELECT PERIOD_DATE, MAX(DATA_VALUE) as max_value
        FROM shop.`macro industry`
        WHERE INDIC_ID='2020101522' 
        AND YEAR(PERIOD_DATE)=2014
        GROUP BY PERIOD_DATE
        ORDER BY max_value DESC) AS max14
ON YEAR(max15.PERIOD_DATE)=Year(max14.PERIOD_DATE)+1
AND MONTH(max15.PERIOD_DATE)=MONTH(max14.PERIOD_DATE);

-- 习题5答案
SELECT SUM(CASE WHEN `Coupon_id` is not null And `Date` 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 shop.ccf_online_stage1_train
WHERE Date_received BETWEEN '2016-06-01' and '2016-06-30';
-- group by 商家
SELECT  Merchant_id,
        SUM(CASE WHEN `Coupon_id` is not null And `Date` 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 shop.ccf_online_stage1_train
WHERE Date_received BETWEEN '2016-06-01' and '2016-06-30'
GROUP BY Merchant_id
Order BY discard_rate DESC;

-- 习题6答案
SELECT `pH`, `residual sugar`,
        RANK() OVER(ORDER BY `residual sugar`) as ranking
FROM shop.`winequality-white`
WHERE `pH`='3.63';

-- 习题7答案
SELECT SUM(MARKET_VALUE) as industry_value,
        TYPE_NAME_EN,
        TYPE_NAME_CN
FROM shop.`market data`
WHERE YEAR(END_DATE) ='2018-12-31'
GROUP BY TYPE_NAME_EN,TYPE_NAME_CN
ORDER BY industry_value DESC
LIMIT 3;
-- 9大公司in top3行业
SELECT *
FROM (SELECT TYPE_NAME_CN,
       TICKER_SYMBOL,
       MARKET_VALUE,
       ROW_NUMBER() OVER(PARTITION BY TYPE_NAME_CN ORDER BY MARKET_VALUE DESC) as value_rank_in_industry
       FROM shop.`market data`) as basedata
WHERE value_rank_in_industry in (1,2,3)
AND TYPE_NAME_CN in (
                        SELECT top3_industry.TYPE_NAME_CN
                        FROM (SELECT SUM(MARKET_VALUE) as industry_value,
                                TYPE_NAME_CN
                                FROM shop.`market data`
                                WHERE YEAR(END_DATE) ='2018-12-31'
                                GROUP BY TYPE_NAME_CN
                                ORDER BY industry_value DESC
                                LIMIT 3) as top3_industry);

-- 习题8答案
SELECT User_id, SUM(use_counts) as total_use
FROM (    SELECT User_id,
                SUM(CASE WHEN Coupon_id IS NOT NULL AND (`Date`BETWEEN '2016-06-01' AND '2016-06-30')
                    THEN 1 ELSE 0 END) AS use_counts
        FROM shop.ccf_online_stage1_train
        GROUP BY User_id
        UNION ALL
        SELECT User_id,
                SUM(CASE WHEN Coupon_id IS NOT NULL AND (`Date`BETWEEN '2016-06-01' AND '2016-06-30')
                    THEN 1 ELSE 0 END) AS use_counts
        FROM shop.ccf_offline_stage1_train
        GROUP BY User_id) AS merged_data
GROUP BY User_id
ORDER BY total_use DESC;

-- 习题9答案
SELECT *
FROM (SELECT TICKER_SYMBOL,
        YEAR(END_DATE) AS `YEAR`,
        QUARTER(END_DATE) AS `QUARTER`,
        SUM(VALUE) as `AMOUNT`
        FROM shop.`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) AS basedata
LEFT OUTER JOIN (SELECT TICKER_SYMBOL,
        YEAR(END_DATE) AS `YEAR`,
        QUARTER(END_DATE) AS `QUARTER`,
        SUM(N_INCOME) as `AMOUNT`
        FROM shop.`income statement`
        GROUP BY TICKER_SYMBOL,YEAR(END_DATE),QUARTER(END_DATE)) AS adddata
ON  basedata.TICKER_SYMBOL=adddata.TICKER_SYMBOL
AND basedata.`YEAR`=adddata.`YEAR`
AND basedata.`QUARTER`=adddata.`QUARTER`;

-- 习题10答案
SELECT Merchant_id, sum(discount_value) AS total_discount_value
FROM (SELECT Merchant_id, SUM(SUBSTRING_INDEX(Discount_rate,':',-1)) as discount_value
        FROM shop.ccf_online_stage1_train
        WHERE Coupon_id is not null
        AND DATE(`Date`) BETWEEN '2016-06-01' AND '2016-06-30'
        GROUP BY Merchant_id
        UNION ALL
        SELECT Merchant_id, SUM(SUBSTRING_INDEX(Discount_rate,':',-1)) as discount_value
        FROM shop.ccf_offline_stage1_train
        WHERE Coupon_id is not null
        AND DATE(`Date`) BETWEEN '2016-06-01' AND '2016-06-30'
        GROUP BY Merchant_id) as totaldata
GROUP BY Merchant_id
ORDER BY sum(discount_value) DESC
LIMIT 3;