SQL题：某外卖平台有users（用户表），restaurants（餐厅表），orders（订单表）

一、进行表的创建

-- 用户表
create table if not exists users (
	user_id int,
    user_name string,
    password string,
    email string,
    phone_number string,
    address string
)
row format delimited
fields terminated by ',';

-- 餐厅表
create table if not exists restaurants (
    restaurant_id int,
    restaurant_name string,
    restaurant_address string,
    restaurant_phonenumber string,
    cuisine_type string
)
row format delimited
fields terminated by ',';

-- 订单表
create table if not exists orders (
    order_id int,
    user_id int,
    restaurant_id int,
    order_date string,
    total_amount string,
    order_status string
)
row format delimited
fields terminated by ',';

二、对需求进行解答

1. 查询五一期间（2023-05-01 ~ 2023-05-07），每个餐厅的订单总数量及排名

with t as (
    -- 五一期间每一个餐厅的订单总数
    select restaurant_id,count(1) totalNum  from orders
         where order_date >='2023-05-01 00:00:00' and order_date <='2023-05-07 23:59:59'
         group by restaurant_id
)
select *,dense_rank() over (order by totalNum desc) from t ;

2. 查看最近一个月内在一家餐厅重复购买 3 次以上的餐厅名字、用户名字、购买次数

with t as (
    select r.restaurant_name,u.user_name,o.order_id from orders o , restaurants r ,users u
     where o.restaurant_id = r.restaurant_id and o.user_id = u.user_id
     and substr(o.order_date,1,10) >= add_months(`current_date`(),-1)
)
select t.restaurant_name,t.user_name,count(1) orderNum from t group by t.restaurant_name,t.user_name having orderNum >=3;

3. 查看订单最多的用户ID、用户名（包含并列第一的情况）

-- 3. 查看订单最多的用户ID、用户名（包含并列第一的情况）

with t as (
    select u.user_id,u.user_name,count(1) ordernum from
        orders o join users u on o.user_id = u.user_id
    group by u.user_id,u.user_name
),t2 as (
    select *,dense_rank() over (order by ordernum desc) pm from t
)
select * from t2 where pm = 1;

 4. 查询至少被三个不同用户消费过的餐厅

select restaurant_id,count(distinct user_id) userNum from orders group by restaurant_id
  having userNum>=3;

5. 查询在五一期间，每个用户消费的餐厅前三名（按照消费的金额计算）

select substr(order_date,6,5) from orders limit 1;
with t as (
   select user_id,restaurant_id,sum(total_amount) orderMoney from orders where substr(order_date,6,5) >='05-01' and substr(order_date,6,5) <='05-07'
                     group by user_id,restaurant_id
),t2 as (
    select *,dense_rank() over (partition by user_id order by orderMoney desc) pm from t
)
select * from t2 where pm <=3;

6. 查询每一个用户在最近一个月内的订单量，以及上一个月的订单量

select
    user_id,
     count(case when substr(order_date,1,10) between add_months(`current_date`(),-1)  and `current_date`() then 1 else null end ) `最近一个月订单数`,
     count(case when substr(order_date,1,10) between add_months(`current_date`(),-2)  and date_sub(add_months(`current_date`(),-1),1) then 1 else null end ) `上个月订单数`

   from orders group by user_id ;

7. 查询每一个餐厅当月订单数量、上月订单数量、订单数量环比增长率，按照环比增长率降序排列

环比增长率：（当月订单数量 - 上月订单数量）/ 上月订单数量

with t as (
    select restaurant_id,
       count(case when substr(order_date,1,10) between add_months(`current_date`(),-1)  and `current_date`() then 1 else null end ) dydds,
       count(case when substr(order_date,1,10) between add_months(`current_date`(),-2)  and date_sub(add_months(`current_date`(),-1),1) then 1 else null end ) sydds
       from orders group by restaurant_id
)
select restaurant_id,(dydds-sydds)/sydds zzlv from t order by zzlv desc ;

8. 查询每个用户的订单金额排名，显示排名前三的用户及其订单金额

with t as (
    select user_id,sum(total_amount) totalMoney from orders group by user_id
),t2 as (
    select *,dense_rank() over (order by totalMoney desc) pm from t
)
select *,u.user_name from t2,users u where t2.user_id=u.user_id and pm <= 3;

9. 查询每一个顾客的总下单数量和总消费金额

select user_id,count(1),sum(total_amount) from orders group by user_id;

10. 查询五一期间，消费总金额高于平均值的用户

select avg(total_amount) from orders where substr(order_date,6,5) >='05-01' and substr(order_date,6,5) <='05-07';

select user_id,sum(total_amount) totalMoney from orders
     where substr(order_date,6,5) >='05-01' and substr(order_date,6,5) <='05-07' group by user_id
     having sum(total_amount) >(select avg(total_amount) from orders where substr(order_date,6,5) >='05-01' and substr(order_date,6,5) <='05-07');

11. 查询最后一个订单距离现在时间超过 5 天的用户，以及天数

select user_id,max(order_date),datediff(`current_date`(),max(order_date)) days from orders group by user_id having max(user_id) < date_sub(`current_date`(),5);

12. 查询具有最多订单的餐厅所在城市的名称和订单数量

with t as (
    select r.restaurant_name,r.restaurant_address,count(1) orderNum from orders o join restaurants r on o.restaurant_id = r.restaurant_id
     group by r.restaurant_name,r.restaurant_address
),t2 as (
    select *,dense_rank() over ( order by orderNum desc) pm from t
)
select * from t2 where pm = 1;