This Leetcode problem Number of Transactions per Visit LeetCode Solution is done in SQL.

List of all LeetCode Solution

Number of Transactions per Visit LeetCode Solution

Problem Statement

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.

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)

A bank wants to draw a chart of the number of transactions bank visitors did in one visit to the bank and the corresponding number of visitors who have done this number of transaction in one visit.

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 result format is in the following example.

Example 1:
Input:

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

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

Output:

transactions_count	visits_count
0	4
1	5
2	0
3	1

Explanation:
– 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

Number of Transactions per Visit LeetCode Solution MySQL

select 
  (
    select 
      0
  ) as transactions_count, 
  count(*) as visits_count 
from 
  Visits 
where 
  (user_id, visit_date) not in (
    select 
      user_id, 
      transaction_date 
    from 
      Transactions
  ) 
union 
select 
  s.transactions_count, 
  if(
    visits_count is null, 0, visits_count
  ) as visits_count 
from 
  (
    select 
      tc as transactions_count 
    from 
      (
        select 
          t.user_id, 
          @tc := @tc + 1 as tc 
        from 
          Transactions as t, 
          (
            select 
              @tc := 0
          ) as u
      ) as s 
    where 
      tc <= (
        select 
          ifnull(
            max(transactions_count), 
            0
          ) 
        from 
          (
            select 
              count(*) as transactions_count 
            from 
              Transactions 
            group by 
              user_id, 
              transaction_date
          ) as t
      )
  ) as s 
  left join (
    select 
      transactions_count, 
      count(*) as visits_count 
    from 
      (
        select 
          count(*) as transactions_count 
        from 
          Transactions 
        group by 
          user_id, 
          transaction_date
      ) as t 
    group by 
      transactions_count
  ) as t on s.transactions_count = t.transactions_count 
order by 
  transactions_count;Code language: SQL (Structured Query Language) (sql)