SQL面试题

1、每个月每张卡连续消费的最大天数(如卡在当月只有一次消费则为1)。连续消费天数:指一段时间内连续每天都有消费，同一天有多笔消费算一天消费，不能跨月份统计。

with t1 as (select distinct card_nbr, c_month, c_date from c_t),
     t2 as (select *, row_number() over (partition by card_nbr,c_month order by c_date) rn from t1),
     t3 as (select *, date_sub(c_date, rn) dt2 from t2),
     -- 此处与之前例题有区别，需要先拿到所有连续消费的情况，而不是直接筛选出连续消费大于N天的记录
     t4 as (select dt2, card_nbr, c_month, count(1) as cnt from t3 group by dt2, card_nbr, c_month),
     -- 不再是去重，而是取出最大的，因为题目要求的是获取 每个月每张卡连续消费的最大天数
     t5 as (select *, row_number() over (partition by card_nbr,c_month order by cnt desc) as rn from t4)
select card_nbr, c_month, cnt from t5 where rn = 1;

2、有两张表TA和TB，关联字段是R NO，并且主键都是R NO，请使用两种或更多的方法找出R NO在TA表中而不在TB表中的所有值

-- not in 
SELECT TA.R_NO
FROM TA
WHERE TA.R_NO NOT IN (
    SELECT TB.R_NO
    FROM TB
);


-- left join 
SELECT TA.R_NO
FROM TA
LEFT JOIN TB ON TA.R_NO = TB.R_NO
WHERE TB.R_NO IS NULL;