蚂蚁数据 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

  2，测试数据

• 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);

  3，解题思路

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

with t1 as(select d1.uid,sum(case when d1.ovd_days>d2.ovd_days then 1 else 0 end) AS grade
from dwd_trd_loan_tb_dd d1 left join dim_ovd_config_dd d2 group by d1.uid)
select t1.uid,ROW_NUMBER() over (order by grade,uid) grade from t1;