蚂蚁数据研发SQL面试题

说明： 每个表的解释

• 有一张用户贷款信息表dwd_trd_loan_tb_dd，包含uid（用户id）、amt（贷款金额）、ovd_days（逾期天数）、dt（时间分区）以及逾期等级配置表dim_ovd_config_dd，包含ovd_days（逾期天数），user_level（用户风险等级）

• 注意：示例如下，当ovd_days=1且user_level=1，表示用户逾期天数<=1时，用户风险等级都为1；当ovd_days=30且user_level=2，表示用户逾期天数>1同时<=30时，用户风险等级为2；

问题：计算20241011日所有贷款用户对应的风险等级

-- 举例如下：
-- 输入
-- dwd_trd_loan_tb_dd
uid   amt   ovd_days     dt
1001  1000     0       20241011
1002  1000     33      20241011
1003  1000     12      20241011
1004  1000     68      20241011
-- dim_ovd_config_dd
ovd_days  user_level
1             1
30            2
60            3
180           4
-- 输出
uid   user_level
1001      1
1002      3
1003      2
1004      4 

结果： 

创建表结构并插入数据

create table dwd_trd_loan_tb_dd (
uid string,
amt int,
ovd_days int,
dt string
);
create table dim_ovd_config_dd (
ovd_days bigint,
user_level bigint
);
INSERT INTO dwd_trd_loan_tb_dd VALUES 
('1001',1000,0,'20241011'),
('1002',1000,33,'20241011'),
('1003',1000,12,'20241011'),
('1004',1000,68,'20241011')
;
INSERT INTO dim_ovd_config_dd VALUES 
(1,1),
(30,2),
(60,3),
(180,4);

第一步：先将两个表关联

select *,
       dwd.ovd_days uovd_days,
       dim.ovd_days lovd_days 
       from dwd_trd_loan_tb_dd dwd join dim_ovd_config_dd dim on 1=1

第二步：直接计算等级

with t as (
   select *,
          dwd.ovd_days uovd_days,
          dim.ovd_days lovd_days 
          from dwd_trd_loan_tb_dd dwd join dim_ovd_config_dd dim on 1=1
)select uid,min(user_level) from t where uovd_days<lovd_days group by uid;