哈罗数据分析（SQL)笔试

数据

导入数据库

先导入数据库：

分别导入：






如下：

同理导入其它几个数据表：

理解数据

品类：cate 品牌：brand 销量：qtty 销额 ：amount 用户ID:pin 商品编号:SKUID

• pin表：dt、skuid、pin三个特征关系。即：时间、用户编号和商品编号。
• sku表：skuid、cate、brand特征之间的关系。即商品编号、品类、品牌之间的关系。
• order表：dt、skuid、sku_name、qtty、amount特征之间的关系。即：时间、商品编号、商品名称、销量、销售额。

品类属于品牌下的一种。比如华为的笔记本。

要求

1、所有单价为0的不计入销量
2、笔记本单价500以下的不计入销量
3、所有产品名称中带荣耀的华为产品，将产品品牌更改为荣耀
4、访客量定义为单日访问该品类下品牌的去重用户数（如某用户id单日浏览了小米的三款产品，该品类下小米品牌访客量记1）
5、19年与18年销量均为1-15号的销量
6、同比数据可以以小数形式呈现，保留两位
7、所有产品名称中含有补差价的产品不计入销量、销额和访客量
8、表格呈现按照19年访客量降序呈现

思路

• 订单表 关联sku表 取出订单的品牌
• sum里面用 if判断是不是19年
• 前两个是where过滤，后面是case when换名，先在子查询里过滤，这个主要考察的是代码风格

我的代码

SELECT
    result2.*,ROUND( result2.19年销量 / result2.18年销量,2) AS 19年销量同比,ROUND( result2.19年销额 / result2.18年销额,2) AS 19年销额同比,ROUND( result2.19年访客量 / result2.18年访客量,2) AS 19年访客量同比 
FROM(
    SELECT
        result.cate 品类,
        result.brand 品牌,SUM(
        CASE
                
                WHEN YEAR( result.order_dt )=2019 
                AND result.unit_price >0 
                AND result.sku_name NOT LIKE '%补差价%' 
                AND DAY( result.order_dt ) BETWEEN 1 
                AND 15AND( CASE WHEN result.cate ='笔记本' AND result.unit_price <500 THEN NULL ELSE result.qtty END ) THEN
                    result.qtty ELSE NULL 
                END 
                ) AS 19年销量,SUM( CASE WHEN YEAR( result.order_dt )=2019 AND result.sku_name NOT LIKE '%补差价%' THEN result.amount ELSE NULL END )19年销额,COUNT( DISTINCT CASE WHEN YEAR( result.pin_dt )=2019 AND result.sku_name NOT LIKE '%补差价%' THEN result.pin ELSE NULL END ) AS 19年访客量,SUM(
                CASE
                        
                        WHEN YEAR( result.order_dt )=2018 
                        AND result.unit_price >0 
                        AND result.sku_name NOT LIKE '%补差价%' 
                        AND DAY( result.order_dt ) BETWEEN 1 
                        AND 15AND( CASE WHEN result.cate ='笔记本' AND result.unit_price <500 THEN NULL ELSE result.qtty END ) THEN
                            result.qtty ELSE NULL 
                        END 
                        ) AS 18年销量,SUM( CASE WHEN YEAR( result.order_dt )=2018 AND result.sku_name NOT LIKE '%补差价%' THEN result.amount ELSE NULL END )18年销额,COUNT( DISTINCT CASE WHEN YEAR( result.pin_dt )=2018 AND result.sku_name NOT LIKE '%补差价%' THEN result.pin ELSE NULL END ) AS 18年访客量 
                    FROM(
                        SELECT
                            sku.cate,
                        CASE
                                
                                WHEN `order`.sku_name LIKE '%荣耀%' THEN
                                '华为' ELSE sku.brand 
                            END brand,
        `order`.skuid,
        `order`.qtty,
        `order`.amount,
        `order`.sku_name,
        `order`.amount / `order`.qtty AS unit_price,
        `order`.dt AS order_dt,
        pin.pin,
        pin.dt AS pin_dt 
    FROM
        sku
        JOIN `order` ON sku.skuid = `order`.skuid
        JOIN pin ON sku.skuid = pin.skuid 
    ) AS result 
GROUP BY
    result.cate,
    result.brand 
    ) AS result2;

结果：