LeetCode 【困难】数据库-第1336（超困难）：每次访问的交易次数

题目

数据

CREATE TABLE Visits(
user_id INT,
visit_date DATE);

CREATE TABLE Transactions1(
user_id INT,
transaction_date DATE,
amount INT);

INSERT INTO Visits VALUE(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'); 

INSERT INTO Transactions1 VALUE(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);

结果

cnt 为amount > 0 的数目

amt 为 amount

n 为transactions 表的行数编号

1.拼接两个数据表，因为字段数不同，故新增一列，且负值为0

select user_id,visit_date dt,0 amt from Visits # visits中没有amount，所以新建一列为amt，并全部赋值为0
union all
select user_id,transaction_date dt,amount amt from Transactions

2.筛选出来amount大于0的个数cnt

select *, sum(amt>0) cnt
from
(   select user_id,visit_date dt,0 amt from Visits # visits中没有amount，所以新建一列为amt，并全部赋值为0
union all
select user_id,transaction_date dt,amount amt from Transactions
) all_data
group by  user_id,dt

3.给交易表 transactions 编号从0开始

 select 0 as n
union all
select (@x := @x+1) as n from Transactions,(select @x := 0) num  # 给每一行编号，从0开始

4 n<= 某天最多交易次数

select max(cnt) 
from 
  (select *,sum(amt>0) cnt
      from
	(   select user_id,visit_date dt,0 amt from Visits
            union all
            select user_id,transaction_date dt,amount amt from Transactions
         ) all_data
group by  user_id,dt
   ) tmp 

5.最终链接两表、floor 向下取整，

select floor(n) transactions_count,
			 count(cnt) visits_count
from 
		(   select *, sum(amt>0) cnt
				from
						(   select user_id,visit_date dt,0 amt from Visits # visits中没有amount，所以新建一列为amt，并全部赋值为0
								union all
								select user_id,transaction_date dt,amount amt from Transactions
						) all_data
				group by  user_id,dt
		) tmp 
		right join
		(      select 0 as n
						union all
						select (@x := @x+1) as n from Transactions,(select @x := 0) num  # 给每一行编号，从0开始
		) nums
		on nums.n = tmp.cnt

		where 
				n <=
							(select max(cnt) from 
								 (select *,sum(amt>0) cnt
									from
											(   select user_id,visit_date dt,0 amt from Visits
													union all
													select user_id,transaction_date dt,amount amt from Transactions
											) all_data
									group by  user_id,dt
									) tmp 
							)
group by n;