leetcode 1336解题思路

题目见：https://leetcode.jp/problemdetail.php?id=1336

Table: Visits

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| visit_date    | date    |
+---------------+---------+
(user_id, visit_date) is the primary key for this table.
Each row of this table indicates that user_id has visited the bank in visit_date.

Table: Transactions

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| user_id          | int     |
| transaction_date | date    |
| amount           | int     |
+------------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table indicates that user_id has done a transaction of amount in transaction_date.
It is guaranteed that the user has visited the bank in the transaction_date.(i.e The Visits table contains (user_id, transaction_date) in one row)

Write an SQL query to find how many users visited the bank and didn’t do any transactions, how many visited the bank and did one transaction and so on.

The result table will contain two columns:

• transactions_count which is the number of transactions done in one visit.
• visits_count which is the corresponding number of users who did transactions_count in one visit to the bank.

transactions_count should take all values from 0 to max(transactions_count) done by one or more users.

Order the result table by transactions_count.

The query result format is in the following example:

Visits table:
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1       | 2020-01-01 |
| 2       | 2020-01-02 |
| 12      | 2020-01-01 |
| 19      | 2020-01-03 |
| 1       | 2020-01-02 |
| 2       | 2020-01-03 |
| 1       | 2020-01-04 |
| 7       | 2020-01-11 |
| 9       | 2020-01-25 |
| 8       | 2020-01-28 |
+---------+------------+
Transactions table:
+---------+------------------+--------+
| user_id | transaction_date | amount |
+---------+------------------+--------+
| 1       | 2020-01-02       | 120    |
| 2       | 2020-01-03       | 22     |
| 7       | 2020-01-11       | 232    |
| 1       | 2020-01-04       | 7      |
| 9       | 2020-01-25       | 33     |
| 9       | 2020-01-25       | 66     |
| 8       | 2020-01-28       | 1      |
| 9       | 2020-01-25       | 99     |
+---------+------------------+--------+
Result table:
+--------------------+--------------+
| transactions_count | visits_count |
+--------------------+--------------+
| 0                  | 4            |
| 1                  | 5            |
| 2                  | 0            |
| 3                  | 1            |
+--------------------+--------------+
* For transactions_count = 0, The visits (1, "2020-01-01"), (2, "2020-01-02"), (12, "2020-01-01") and (19, "2020-01-03") did no transactions so visits_count = 4.
* For transactions_count = 1, The visits (2, "2020-01-03"), (7, "2020-01-11"), (8, "2020-01-28"), (1, "2020-01-02") and (1, "2020-01-04") did one transaction so visits_count = 5.
* For transactions_count = 2, No customers visited the bank and did two transactions so visits_count = 0.
* For transactions_count = 3, The visit (9, "2020-01-25") did three transactions so visits_count = 1.
* For transactions_count >= 4, No customers visited the bank and did more than three transactions so we will stop at transactions_count = 3

题目很长，简单来说就是给了两张表， 一张visits, 一张Transactions。 一次visit可以有0次或者多次transaction. 现在题目就是要统计transaction 次数在0 ~ max(transaction count)对应的visit次数。
有点绕口。根据例子把中间过程写出来，见下图
第一步先把两张表join起来。这里思考一下用什么join以及key？答案见文末题目1
第二步统计每次visit的transaction
第三步统计每个transaction count对应的visit count
最后就是实现0 ～ max(transactions_count)

/* 1. join visit table and transaction table by user_id and date */
/* 2. count transaction per visit */
/* 3. count users per visit */


/* count users per visit */
select id as transaction_count, case when visit_count is null then 0 else 1 end from (

select transaction_count, count(user_id) as visit_count
from(
    /* count transaction per visit */
    select user_id, visit_date, count(transaction_date) as transaction_count
    from (
	
        /* left join visit table and transaction table by user_id and date */
        select v.user_id, visit_date, transaction_date, amount
        from   visits v
        left join transactions t
        on v.user_id = t.user_id and visit_date = transaction_date

    ) vt
    group by user_id, visit_date

) group by visit_count
) t
/* generate id from 0 */
right join (
    select (@cnt := @cnt + 1) as id
    from transactions
    cross join (select @cnt := -1)  
    /*in case transaction table is empty*/
    union select 0
) tid
on t.transaction_count = tid.id

where tid.id <= (
/*in case transaction table is empty*/
select 0 as cnt
union
/*get max transaction count*/
select count(1) as cnt
from   transactions
group by user_id, transaction_date
order by cnt desc
limit 1
)

1. 用什么join 两张表？
   left join, 因为一次visit可以不进行transaction。join的键是user_id, vistie_date。 因为一次visit通过user_id, vistie_date确定。

代码仅供参考，要是跑不通别怪我。我没有钱订阅leetcode premium

参考：
https://code.dennyzhang.com/number-of-transactions-per-visit