# $1336. 每次访问的交易次数
# https://leetcode-cn.com/problems/number-of-transactions-per-visit/

# SQL架构
Create table If Not Exists Visits (user_id int, visit_date date);
Create table If Not Exists Transactions (user_id int, transaction_date date, amount int);
Truncate table Visits;
insert into Visits (user_id, visit_date) values ('1', '2020-01-01');
insert into Visits (user_id, visit_date) values ('2', '2020-01-02');
insert into Visits (user_id, visit_date) values ('12', '2020-01-01');
insert into Visits (user_id, visit_date) values ('19', '2020-01-03');
insert into Visits (user_id, visit_date) values ('1', '2020-01-02');
insert into Visits (user_id, visit_date) values ('2', '2020-01-03');
insert into Visits (user_id, visit_date) values ('1', '2020-01-04');
insert into Visits (user_id, visit_date) values ('7', '2020-01-11');
insert into Visits (user_id, visit_date) values ('9', '2020-01-25');
insert into Visits (user_id, visit_date) values ('8', '2020-01-28');
Truncate table Transactions;
insert into Transactions (user_id, transaction_date, amount) values ('1', '2020-01-02', '120');
insert into Transactions (user_id, transaction_date, amount) values ('2', '2020-01-03', '22');
insert into Transactions (user_id, transaction_date, amount) values ('7', '2020-01-11', '232');
insert into Transactions (user_id, transaction_date, amount) values ('1', '2020-01-04', '7');
insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '33');
insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '66');
insert into Transactions (user_id, transaction_date, amount) values ('8', '2020-01-28', '1');
insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '99');

# Write your MySQL query statement below
with recursive a as(
    select
        0 as n
    union
    all
    select
        n + 1
    from
        a
    where
        n < (
            select
                max(transactions_count)
            from
                (
                    select
                        count(t.amount) as transactions_count
                    from
                        Visits v
                        left join Transactions t on v.user_id = t.user_id
                        and v.visit_date = t.transaction_date
                    group by
                        v.user_id,
                        v.visit_date
                ) tmp
        )
)
select
    n as transactions_count,
    ifnull(visits_count, 0) as visits_count
from
    a
    left join (
        select
            transactions_count,
            count(*) as visits_count
        from
            (
                select
                    count(t.amount) as transactions_count
                from
                    Visits v
                    left join Transactions t on v.user_id = t.user_id
                    and v.visit_date = t.transaction_date
                group by
                    v.user_id,
                    v.visit_date
            ) tmp
        group by
            transactions_count
        order by
            transactions_count
    ) tmp on n = transactions_count;

# clean-up
drop table Visits;
drop table Transactions;