请教一题SQL leetcode 550

550是prime的题目，我就找了题目来做。

题目是这样的
Write an SQL query that reports the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
The query result format is in the following example：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction |
+-----------+
| 0.33 |
+-----------+


我在网上看到2个solution, 都有些迷糊。恳请大牛回答。

第一个solution

1. SELECT ROUND(COUNT(DISTINCT a1.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
   
2. FROM Activity AS a1, Activity AS a2
   
3. WHERE a1.player_id = a2.player_id
   
4.     AND DATEDIFF(a1.event_date, a2.event_date) = -1
   
5.     AND (a1.player_id, a1.event_date) IN
   
6.         (SELECT player_id, MIN(event_date)
   
7.         FROM Activity
   
8.         GROUP BY player_id)

复制代码

我的问题是

1. 请问COUNT(DISTINCT a1.player_id) 和SELECT COUNT(DISTINCT player_id) FROM Activity 有什么区别？ 就算用了alias, 也好像都是用的activity这张表，没有进行过变动。
2. 还是和第一行有关，是不是这个solution使用了3个表，其中两个activity用了两个alias?



第二个solution使用的是inner join

1. select round(sum(case when t1.event_date = t2.first_event+1 then 1 else 0 end)/count(distinct t1.player_id), 2) as fraction
   
2. from Activity as t1 inner join
   
3.     (select player_id, min(event_date) as first_event
   
4.     from Activity
   
5.     group by player_id) as t2
   
6. on t1.player_id = t2.player_id

复制代码

我的问题：
3. 这里的inner join 没明白。这里t1和t2做 inner join的话，不就是t2自己吗？

event date不一样

补充内容 (2020-9-19 04:32):
第一个solution的分子是连续的distinct player，是从cross join的那个表，分母是所有distinct player，从activity那个表

按照我的理解
1. 第一个是必须要用a1.，要不然的话后面两个表a1, a2里都有player_id无法区分，而(SELECT COUNT(DISTINCT player_id) FROM Activity), 2)这个子查询只是为了算出分母而已，子查询内没有重名的担忧
2. 是的，a1,a2是主要的两个表，用来选择出满足符合条件的行，而作为分母的那个表其实只是为了得到分母而已，相对独立

3. 不是，t2是选择以后的，相当于每一个player只有一行了，而t1是原表，连接以后的表的尺寸大概是相当于t1原表的基础上增加了一列作为first_event（注明：也就是说同一个player，多个event_date使用同样的first_event），然后在这个基础上进行case when

分清一下，join都是两个表在做行为。