1336. 每次访问的交易次数


这一题需要用到 RECURSIVE 递归查询

WITH RECURSIVE t AS ( 
    SELECT 0 as n
  UNION ALL
    SELECT n+1 FROM t WHERE n < 
        (SELECT count(*) FROM Transactions GROUP BY user_id,transaction_date 
        order by count(*) desc limit 1)                                                         
)
SELECT n transactions_count, count(cnt) visits_count FROM t
LEFT JOIN
(
SELECT count(transaction_date) cnt FROM Visits V LEFT JOIN Transactions T
ON (V.user_id,visit_date) = (T.user_id,transaction_date)
GROUP BY V.user_id,visit_date
) A
ON n = cnt GROUP BY n