SQL窗口函数/多个子查询问题

Question

题目描述

已知表格：
daily_sales：2021年每日销量表，
有date，shop_id，order_id三列
1）算出2021-12-01销量前3的shop_id、销量quantity、排名rank
2）求每shop_id达到全年销量80%的日期：shop_id、date_80p
3）求每天每shop_id的历史30天销量：date、shop_id、quantity_30d

我的代码

第一问：我是想先套一张表t1求出2021-12-01每个shop id下order id的数量，再套一张t2表用row number函数得到降序quantity排列的rank，再选取前三名

SELECT shop_id,
       quantity,
       rank
    from (
        SELECT shop_id,
               quantity,
               ROW_NUMBER()over(partition by shop_id order by quantity desc) rank
            from(
                SELECT date,
                       shop_id,
                       COUNT(order_id)quantity
                    from daily_sales
                    where date = '2021-12-01'
                    group by shop_id
            )t1
    )t2 
where rank <= 3  

第二问：我是想用t1求出每个shop id的总销量，再用t2求出每个shop id每日的销量，再用where过滤出日销量大于等于总销量的日期

select shop_id,
       date as date_80p
    from(
        select date,
               shop_id,
               order_id,
               count(order_id)daily_quantity,
               full_quantity
            from(
        select date,
               shop_id,
               order_id,
               count(order_id)full_quantity
            from daily_sales
            group by shop_id
            )t1
        group by date,shop_id
    )t2
where daily_quantity >= 0.8*full_quantity

第三问：我是用日期差得到所有日期大于前30天的date，并用groupby得到每日每shopid的销量，我总觉得这里逻辑好像有问题，希望可以得到帮助。

select date,
          shop_id,
          count(order_id) quantity_30d
     from daily_sales
     where date(date) > (
    select date_sub(max(date)),interval 30 day)
    from daily_sales)
    group by date, shop_id

我的疑问

我不知道自己的思路和代码是否正确，如有错误或者更高效的思路，恳请帮助和指出！非常感谢您！

Answer 1

问题一，可以不用子查询。

      SELECT
         shop_id,
         COUNT(order_id) quantity,
         (@i:=@i+1) rank
         from daily_sales,(SELECT @i:=0) AS t
         where date = '2021-12-01'
         group by shop_id order by COUNT(order_id) desc limit 3