第三关
国庆假期期间,因疫情原因,游客或航空公司会取消行程,现需要统计每日退票情况,并在连续两天退票率出现下降时调整新的票价策略。
退票率计算规则如下:(被航空公司或游客取消的绿码游客生成的订单数量) / (绿码游客生成的订单总数)。
写一段 SQL 语句查出 “2022-10-01” 至 “2022-10-05” 期间绿码游客的退票率。绿码游客即 banned
为 No 的用户,非绿码游客即 banned
为 Yes 的用户。并判断当日是否触发新的票价策略。
返回结果表中的数据按订票时间降序组织。其中退票率需要四舍五入保留两位小数。
建表
create table "Orders"
(
id int,
users_id int,
company_id int,
city_id int,
status varchar(30),
request_at date
);
create table "Users"
(
users_id int,
banned varchar(30)
);
create table "Companies"
(
company_id int,
name varchar(30)
);
Orders 是订单表,表中id
是这张表的主键。这张表中存放所有航空公司的订票信息。每张机票有唯一id
,其中 users_id
是 Users
表中 users_id
的外键。company_id
是Companies
表中company_id
的外键,status
是一个表示机票状态的枚举类型,枚举成员为(‘completed’, ‘cancelled_by_company’, ‘cancelled_by_user’) 。
Users
是用户表,表中users_id
是这张表的主键,banned
是一个表示用户是否被禁止的枚举类型,枚举成员为 (‘Yes’, ‘No’) 。
Companies表是航空公司表,表中id
是这张表的主键。
插入数据
insert into "Orders" values
(1, 1, 10, 1, 'completed', '2022-10-01'),
(2, 2, 11, 1, 'cancelled_by_company', '2022-10-01'),
(3, 3, 12, 6, 'completed' ,'2022-10-01'),
(4, 4, 13, 6, 'cancelled_by_user' ,'2022-10-01'),
(5, 1, 10, 1, 'completed' ,'2022-10-02'),
(6, 2, 11, 6, 'completed' , '2022-10-02'),
(7, 3, 12, 6, 'completed' , '2022-10-02'),
(8, 2, 12, 12, 'completed' , '2022-10-03'),
(9, 3, 10, 12, 'completed' , '2022-10-03'),
(10,4 ,13, 12, 'cancelled_by_company' ,'2022-10-03'),
(11, 1, 12, 12, 'completed' , '2022-10-04'),
(12, 2, 10, 12, 'completed' , '2022-10-04'),
(13,3 ,13, 12, 'cancelled_by_company' ,'2022-10-04'),
(14,4 ,13, 12, 'completed' ,'2022-10-04'),
(15, 1, 12, 12, 'completed' , '2022-10-05'),
(16, 2, 10, 12, 'completed' , '2022-10-05'),
(17,3 ,13, 12, 'completed' ,'2022-10-05'),
(18,4 ,13, 12, 'completed' ,'2022-10-05');
insert into "Users" values
(1, 'No'),
(2, 'Yes'),
(3, 'No'),
(4, 'No');
insert into "Companies" values
(10, '东方航空'),
(11, '吉祥航空'),
(12, '深圳航空'),
(13, '厦门航空');
要求输出:
日期 | 今日退票率 | 调整策略
------------+------------+----------
2022-10-05 | 0.00 | t
2022-10-04 | 0.33 | f
2022-10-03 | 0.50 | f
2022-10-02 | 0.00 | f
2022-10-01 | 0.33 | f
(5 rows)
本关参与方式
活动期间10月19日-10月24日,只要在论坛活动帖(地址:点击下方阅读原文)下方评论区贴出截图(截图需为在MemFire Cloud SQL编辑器中的效果),然后带着自己的答案找管理员
核兑抽奖机会哦~
🌟 本题为闭卷模式,被公开的答案有成绩作废的风险哦~