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 varchar(20),
amt bigint,
ovd_days bigint,
dt varchar(20)
);
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);

 思路分析：
看到多张表，先进行JOIN，但是一眼看去好像只能用逾期天数进行关联，可以又无法直接关联，那么就笛卡尔积（考虑到配置表很小）
这时候我们就需要判断每个用户的逾期天数是否小于所有配置的逾期天数，如果是则记为1，这时候会出现一个用户对应多个1，我们要取对应配置逾期天数最小的那一条，怎么办？
按照uid进行分组，配置逾期天数进行排序，对标志位进行求和，最后取开窗结果为1的行记录即可

-- 注意：示例如下，当ovd_days=1且user_level=1，表示用户逾期天数<=1时，用户风险等级都为1；
-- 当ovd_days=30且user_level=2，表示用户逾期天数>1同时<=30时，用户风险等级为2；
-- 问题：计算20241011日所有贷款用户对应的风险等级
select
    t1.uid,
    min(t2.user_level) as user_level
from
    dwd_trd_loan_tb_dd t1
left join
    dim_ovd_config_dd t2 on t1.ovd_days <= t2.ovd_days
where
    t1.dt = '20241011'
group by
    t1.uid;

select
    t1.uid,
    case
        when t1.ovd_days <= 1 then 1
        when t1.ovd_days > 1  and t1.ovd_days <= 30 then 2
        when t1.ovd_days > 30 and t1.ovd_days <= 60 then 3
        when t1.ovd_days > 60 and t1.ovd_days <= 180 then 4
        else 5
    end as user_level
from
    dwd_trd_loan_tb_dd t1
where
    t1.dt = '20241011';