题解 | SQL 177. 国庆期间近7日日均取消订单量

国庆期间近7日日均取消订单量

https://www.nowcoder.com/practice/2b330aa6cc994ec2a988704a078a0703

WITH t1 AS (
    SELECT
        DATE (order_time) AS dt,
        start_time
    FROM tb_get_car_order
    HAVING dt BETWEEN '2021-09-25' AND '2021-10-03'
),
t2 AS (
    SELECT
        dt,
        SUM(IF(start_time IS NULL,0,1)) AS yes_num,
        SUM(IF(start_time IS NULL,1,0)) AS no_num
    FROM t1
    GROUP BY dt
    ORDER BY dt
),
t3 AS (
    SELECT max(dt) AS dt,
           ROUND(sum(yes_num) / 7,2) AS finish_num_7d,
           ROUND(sum(no_num) / 7,2) AS cancel_num_7d
    FROM t2
    WHERE dt BETWEEN '2021-09-25' AND '2021-10-01'
    UNION ALL
    SELECT max(dt) AS dt,
           ROUND(sum(yes_num) / 7,2) AS finish_num_7d,
           ROUND(sum(no_num) / 7,2) AS cancel_num_7d
    FROM t2
    WHERE dt BETWEEN '2021-09-26' AND '2021-10-02'
    UNION ALL
    SELECT max(dt) AS dt,
           ROUND(sum(yes_num) / 7,2) AS finish_num_7d,
           ROUND(sum(no_num) / 7,2) AS cancel_num_7d
    FROM t2
    WHERE dt BETWEEN '2021-09-27' AND '2021-10-03'
)SELECT * FROM t3;

# 这道题的关键在于结果记得除以 7 day!

全部评论

相关推荐

看到这个内容真是闹麻了。。。。。。现在有了AI以后很多人面试都会作弊吗? 那对老老实实面试的人岂不是不公平....
程序员牛肉:公平那是对小孩子讲的童话故事,成年人的世界只有能不能接受失败的后果。 你要是能接受面试作弊被发现之后多家公司联合永久拉黑的后果,你就搞。
点赞 评论 收藏
分享
07-02 13:52
武汉大学 golang
骗你的不露头也秒
牛客87776816...:😃查看图片
点赞 评论 收藏
分享
06-07 19:59
门头沟学院 C++
补药卡我啊😭:都快15年前的了还在11新特性
你的简历改到第几版了
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务