#1336.每次访问的交易次数

这题要求银行客户在一次访问是的交易次数,Visits表是记录着客户id以及访问时间,Transactions表记录着每个客户id,及交易时间与金额,所以将Visits表与Transactions表按照客户id与时间相关联,其中Visits表作主表左关联Transactions表,这样Transactions1不存在的数据就会补null值,null的数据就是访问了银行却没有交易的客户,就可以统计出记录用户的交易次数,以及每次的访问次数,即

select v.user_id,visit_date,count(t.user_id) transactions_count from visits v left join transactions t

on v.visit_date = t.transaction_date and v.user_id=t.user_id

group by user_id,visit_date

因为要求,从最大交易数往后的每一交易数都要记录,但是有可能出现有的交易数,没有访问,也就没有记录数据,将不会统计的情况,所以需要使用递归找到最大交易数,递归出一个从0到最大交易数的数列表,即

with recursive cte (n) as

(

    select 0

    union

    select n + 1 from cte where n <

    (select count(*) from transactions group by user_id, transaction_date order by count(*) desc limit 1)

)

将这两个表使用union相拼接,就能记录出没有访问的交易数,即

with recursive cte (n) as

(

    select 0

    union

    select n + 1 from cte where n <

    (select count(*) from transactions group by user_id, transaction_date order by count(*) desc limit 1)

),

  a as

(select v.user_id,visit_date,count(t.user_id) transactions_count from visits v left join transactions t

on v.visit_date = t.transaction_date and v.user_id=t.user_id

group by user_id,visit_date),

b as

(select transactions_count,count(1) visits_count from a group by transactions_count

union

select n,0 from cte)

最后对于 交易数进行分组,求和出访问数就能得出结果

with recursive cte (n) as

(

    select 0

    union

    select n + 1 from cte where n <

    (select count(*) from transactions group by user_id, transaction_date order by count(*) desc limit 1)

),

  a as

(select v.user_id,visit_date,count(t.user_id) transactions_count from visits v left join transactions t

on v.visit_date = t.transaction_date and v.user_id=t.user_id

group by user_id,visit_date),

b as

(select transactions_count,count(1) visits_count from a group by transactions_count

union

select n,0 from cte)

select transactions_count,sum(visits_count) visits_count from b group by transactions_count order by transactions_count;