连续登录过，为什么还不算次日留存？
有一个玩家，登录日期是这样：
2月28日 2月29日 3月1日 3月2日 登录 没来 登录 登录他在3月1日、2日连续登录了。那么他算不算次日留存的玩家？
先别急着写“相邻两条记录日期差1”。如果要求是第一次登录后的第二天有没有回来，我们该检查的其实是2月29日，后面的连续两天不能补上这次缺席。
这就是 LeetCode 550：Game Play Analysis IV 要处理的问题：每位玩家第一次登录后的次日再次登录，算一位回访玩家；用回访人数除以总玩家人数，四舍五入保留两位小数。原表按玩家和日期唯一，一条记录代表当天登录，游戏局数可以为0。
我们用自己的小数据，看看查询是怎么从这个意思长出来的。
有6条日志，但只有3个玩家
下面在独立的学习数据库里运行，使用 MySQL 8.0。先选好测试库；若已有名为Activity的表，不要覆盖或删除它，换一个空的学习库。建表不加IF NOT EXISTS，让重名及时报错。
CREATE TABLE Activity ( player_id INT NOT NULL, device_id INT NOT NULL, event_date DATE NOT NULL, games_played INT NOT NULL, PRIMARY KEY (player_id, event_date) ); INSERT INTO Activity VALUES (7, 1, '2024-02-28', 3), (7, 2, '2024-02-29', 0), (42, 1, '2024-02-28', 1), (42, 1, '2024-03-01', 4), (42, 2, '2024-03-02', 2), (90, 3, '2024-12-31', 5);把日期按玩家排开：
7号： 2月28日 -> 2月29日 42号：2月28日 -> 3月1日 -> 3月2日 90号：12月31日7号第二天回来了；42号是开头那个反例；90号没有第二条登录记录。所以应该是1个人回访，总共3个人，结果为0.33。
不是1除以6。六行是登录日志，同一个人多来几天，会多几条日志，但不会因此变成几个不同玩家。
先让每个玩家只剩一行
要知道42号该检查哪一天，得先找到他的第一次登录。按玩家分组，取最早的日期就可以：
SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ORDER BY player_id;结果是：
7 2024-02-28 42 2024-02-28 90 2024-12-31这张结果表的变化很重要：原来每个玩家可能有很多行，现在每个人恰好一行。
接下来要找的日期也确定了：7号找2月29日，42号找2月29日，90号找下一年的1月1日。我们不用再考虑所有日期两两之间的关系了，每个玩家只有一个目标日期。
SQL里可以给刚才这张结果起个名字：
Activity原日志 | | 按玩家取最早日期 v first_login：每人一行 | | 各自日期再加一天 v 回到Activity找对应玩家、对应日期下面用的WITH first_login AS (...)就是定义这份命名查询结果，便于后面接着使用。不是又创建了一张需要手动维护的业务表。
找不到回访的玩家，也要留在分母里
我们把目标日期与原日志关联起来，先不算比例，看看究竟找到了什么：
WITH first_login AS ( SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ) SELECT f.player_id, f.first_date, DATE_ADD(f.first_date, INTERVAL 1 DAY) AS expected_date, a.event_date AS returned_date FROM first_login AS f LEFT JOIN Activity AS a ON a.player_id = f.player_id AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY) ORDER BY f.player_id;读7号这一行：第一次是2月28日，想找2月29日，确实找到了2月29日。
再看42号：想找的同样是2月29日，但记录里没有。returned_date为 NULL。后面那两次登录都不是这一次关联的目标。
7号： 首次2/28 -> 找2/29 -> 找到 42号：首次2/28 -> 找2/29 -> NULL 90号：首次12/31 -> 找次年1/1 -> NULL 保留3行，只有第1行真实匹配这里用左连接，是因为没有回来的人也属于总玩家。若用内连接，42号和90号会先被删掉，再在剩下的一行里算比例，就很容易把1/3算成1/1。
为什么关联后每人仍然只有一行，而没有像前面的考试次数题那样接出重复记录？因为题目的(player_id, event_date)唯一：同一个玩家在目标日期至多匹配一行。这也是后面能直接数行的重要条件。
现在才来数分子和分母
上面已经保留了3个人。用COUNT(*)数这些行，得到分母3；用COUNT(a.player_id)数右边真正匹配的玩家，得到分子1。
WITH first_login AS ( SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ) SELECT COUNT(a.player_id) AS returned_players, COUNT(*) AS total_players FROM first_login AS f LEFT JOIN Activity AS a ON a.player_id = f.player_id AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY);本例输出1、3。
右侧匹配不到时，a.player_id为 NULL，COUNT(a.player_id)不计它；真实的player_id又是不允许 NULL 的，所以这里可以拿它识别是否匹配。
在考试报表里，我们说不能用COUNT(*)作为参加次数。这里却可以把它当分母，并不矛盾：这次就是要数保留下来的所有玩家，包括没回来的玩家。先确定想数什么，再选统计表达式，不是给某一种COUNT永远贴上“正确”或“错误”。
最后用分子除以分母，再保留两位小数：
WITH first_login AS ( SELECT player_id, MIN(event_date) AS first_date FROM Activity GROUP BY player_id ) SELECT ROUND(COUNT(a.player_id) / COUNT(*), 2) AS fraction FROM first_login AS f LEFT JOIN Activity AS a ON a.player_id = f.player_id AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY);得到0.33。这里使用 MySQL 的/，不要换成DIV整数除法，否则1除以3会先变成0。
回头检查那条容易写错的路
如果不先找首次日期，而是直接找任意相邻两天：
SELECT ROUND(COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction FROM Activity AS a JOIN Activity AS b ON a.player_id = b.player_id AND b.event_date = DATE_ADD(a.event_date, INTERVAL 1 DAY);这份数据会得到0.67。它把7号和42号都数进去了。
这条查询可以回答“哪些玩家曾经连续两天登录过”，却没有保证这两天从第一次登录开始。DISTINCT只能避免同一个玩家数几次，不能把检查日期变回第一次登录的次日。错误不在去重没加够，而在前面选择的时间起点。
还有两处小条件别顺手加上：7号次日games_played=0，仍然有登录记录；而且他的设备从1换到2，也不影响这道题对玩家回访的判断。我们统计的是玩家登录，不是正局数游戏行为，也不是同设备回访。
日期加一天，不是日期里的数字加1
2月28日的下一天，在2024年是2月29日；2月29日再往后是3月1日。到12月31日，还要进入下一年。
SELECT DATE_ADD(CAST('2024-02-28' AS DATE), INTERVAL 1 DAY), DATE_ADD(CAST('2024-02-29' AS DATE), INTERVAL 1 DAY), DATE_ADD(CAST('2023-02-28' AS DATE), INTERVAL 1 DAY), DATE_ADD(CAST('2026-12-31' AS DATE), INTERVAL 1 DAY);本次结果依次是2024-02-29、2024-03-01、2023-03-01、2027-01-01。这也是为什么直接写DAY(日期)+1再比较日号是不够的。日期运算规则可查 MySQL 日期函数文档。
题目已经给的是 DATE。若真实日志存的是时间戳，还要先确定业务日和时区，不能不加说明地把24小时后的时间点当作“次日登录”。
真正做报表时，还要多问一句
假如今天是10月20日，某位玩家今天才首次登录。现在没有他10月21日的记录，是不是就能认定他没回来？
当然不能，第二天还没到。
本题直接基于给定日期表统计；实际留存报表却必须定义数据截至时间、完整的观察窗口，以及哪些首次登录日期已经有资格参加统计。不能把“还没来得及观察”当成“已经观察过而未回访”，也不能随手用今天的时间过滤一半日志，让分子和分母来自不同范围。
这和开头找首次日期是同一件事：SQL之前先把要检查的那个人、那一天和能观察到的范围说清楚。
附：验证不只拿0.33对一下
本次使用 Ubuntu WSL 中独立的 MySQL 8.0.46 实例，不连接已有数据库。程序从文章提取实际 SQL，核对首次日期、关联明细、人数、正确比例、错误查询和日期边界。
再枚举两个玩家、四天内所有登录组合，分别选择覆盖闰年二月底和跨年的日期段。是否登录、哪天首次、首次次日是否在集合里，由 Python 的日期集合独立判断，不用另写一条同结构 SQL 生成预期。
另外有正文三人样例、空表和四个日历边界。空表时没有总玩家，查询得到 NULL；这里没有把“未定义的比例”随意改成0。实际业务若有不同空数据约定，应明确制定。
还会故意改成：任意连续两天、以最后一天代替首次、除以日志行数、内连接、检查首次当天、要求局数大于0、只给日号加1。这些错误都必须在对应反例里暴露出来，才说明测试区分了我们关心的条件。
下面两份文件放在同一专用实验目录，执行python3 verify_retention.py。需要现有 Linux MySQL 8.0 二进制和运行库；BLOG_MYSQL_ROOT指向包含usr/bin/mysql、usr/sbin/mysqld的解包目录，默认路径对应本地测试包。
脚本新建自己的数据目录，通过随机名 Unix socket 通信，关闭 TCP 与 MySQL X。空密码只用于这次隔离实例，不能拿来部署服务器；不会安装、替换或连接你现有的数据库服务。结束后关闭实例，日志保留。
article-steps.json：
[ { "id": "setup", "sql": "CREATE TABLE Activity (\n player_id INT NOT NULL,\n device_id INT NOT NULL,\n event_date DATE NOT NULL,\n games_played INT NOT NULL,\n PRIMARY KEY (player_id, event_date)\n);\nINSERT INTO Activity VALUES\n (7, 1, '2024-02-28', 3),\n (7, 2, '2024-02-29', 0),\n (42, 1, '2024-02-28', 1),\n (42, 1, '2024-03-01', 4),\n (42, 2, '2024-03-02', 2),\n (90, 3, '2024-12-31', 5);", "expected": [] }, { "id": "first", "sql": "SELECT player_id, MIN(event_date) AS first_date\nFROM Activity\nGROUP BY player_id\nORDER BY player_id;", "expected": [ "7\t2024-02-28", "42\t2024-02-28", "90\t2024-12-31" ] }, { "id": "joined", "sql": "WITH first_login AS (\n SELECT player_id, MIN(event_date) AS first_date\n FROM Activity\n GROUP BY player_id\n)\nSELECT f.player_id, f.first_date,\n DATE_ADD(f.first_date, INTERVAL 1 DAY) AS expected_date,\n a.event_date AS returned_date\nFROM first_login AS f\nLEFT JOIN Activity AS a\n ON a.player_id = f.player_id\n AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)\nORDER BY f.player_id;", "expected": [ "7\t2024-02-28\t2024-02-29\t2024-02-29", "42\t2024-02-28\t2024-02-29\tNULL", "90\t2024-12-31\t2025-01-01\tNULL" ] }, { "id": "counts", "sql": "WITH first_login AS (\n SELECT player_id, MIN(event_date) AS first_date\n FROM Activity\n GROUP BY player_id\n)\nSELECT COUNT(a.player_id) AS returned_players,\n COUNT(*) AS total_players\nFROM first_login AS f\nLEFT JOIN Activity AS a\n ON a.player_id = f.player_id\n AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY);", "expected": [ "1\t3" ] }, { "id": "final", "sql": "WITH first_login AS (\n SELECT player_id, MIN(event_date) AS first_date\n FROM Activity\n GROUP BY player_id\n)\nSELECT ROUND(COUNT(a.player_id) / COUNT(*), 2) AS fraction\nFROM first_login AS f\nLEFT JOIN Activity AS a\n ON a.player_id = f.player_id\n AND a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY);", "expected": [ "0.33" ] }, { "id": "wrong-any", "sql": "SELECT ROUND(COUNT(DISTINCT a.player_id) /\n (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction\nFROM Activity AS a\nJOIN Activity AS b\n ON a.player_id = b.player_id\n AND b.event_date = DATE_ADD(a.event_date, INTERVAL 1 DAY);", "expected": [ "0.67" ] }, { "id": "dates", "sql": "SELECT DATE_ADD(CAST('2024-02-28' AS DATE), INTERVAL 1 DAY),\n DATE_ADD(CAST('2024-02-29' AS DATE), INTERVAL 1 DAY),\n DATE_ADD(CAST('2023-02-28' AS DATE), INTERVAL 1 DAY),\n DATE_ADD(CAST('2026-12-31' AS DATE), INTERVAL 1 DAY);", "expected": [ "2024-02-29\t2024-03-01\t2023-03-01\t2027-01-01" ] } ]verify_retention.py：
import getpass import itertools import json import os from collections import defaultdict from datetime import date, timedelta from decimal import Decimal, ROUND_HALF_UP from pathlib import Path import subprocess import tempfile import time base = Path(__file__).resolve().parent steps = json.loads((base / 'article-steps.json').read_text()) package = Path(os.environ.get('BLOG_MYSQL_ROOT', '/mnt/d/blog-ops/experiments/2026-10-03-candidates/mysql-packages/root')) env = dict(os.environ, LD_LIBRARY_PATH=str(package / 'usr/lib/x86_64-linux-gnu')) run = Path(tempfile.mkdtemp(prefix='retention-', dir=base)) (run / 'data').mkdir() sock = Path('/dev/shm') / (run.name + '.sock') args = [str(package / 'usr/sbin/mysqld'), '--no-defaults', '--basedir=' + str(package / 'usr'), '--datadir=' + str(run / 'data'), '--lc-messages-dir=' + str(package / 'usr/share/mysql'), '--user=' + getpass.getuser(), '--secure-file-priv=NULL', '--innodb-buffer-pool-size=67108864', '--innodb-redo-log-capacity=16777216', '--log-error=' + str(run / 'server.log')] init = subprocess.run(args + ['--initialize-insecure'], env=env, capture_output=True, text=True, timeout=90) if init.returncode: raise RuntimeError(init.stderr) client = [str(package / 'usr/bin/mysql'), '--no-defaults', '--protocol=SOCKET', '--socket=' + str(sock), '-u', 'root', '--batch', '--skip-column-names', '--connect-timeout=2'] server = subprocess.Popen(args + ['--skip-networking', '--mysqlx=0', '--socket=' + str(sock), '--pid-file=' + str(run / 'mysql.pid'), '--tmpdir=' + str(run)], env=env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL) record = {'passed': False, 'run': str(run)} def sql(text): result = subprocess.run(client, input=text, env=env, capture_output=True, text=True, timeout=60) if result.returncode or result.stderr.strip(): raise RuntimeError(result.stdout + result.stderr) return result.stdout.splitlines() def oracle(rows): players = defaultdict(set) for player, _, day, _ in rows: players[player].add(date.fromisoformat(day)) if not players: return ['NULL'] returned = sum(min(days) + timedelta(days=1) in days for days in players.values()) ratio = (Decimal(returned) / Decimal(len(players))).quantize(Decimal('.01'), rounding=ROUND_HALF_UP) return [str(ratio)] def insert(rows): # These generated identifiers and dates never come from external SQL input. if not rows: return '' values = ','.join(f"({p},{d},'{day}',{games})" for p, d, day, games in rows) return 'INSERT INTO Activity VALUES ' + values + ';' try: for _ in range(200): if server.poll() is not None: raise RuntimeError((run / 'server.log').read_text()) if sock.exists(): try: sql('SELECT 1;') break except RuntimeError: pass time.sleep(0.1) else: raise TimeoutError('MySQL startup timed out') record['version'] = sql('SELECT VERSION();')[0] commands = ['CREATE DATABASE blog_retention; USE blog_retention;'] for index, step in enumerate(steps): commands.extend([f"SELECT '__step_{index}__';", step['sql']]) chunks = [] for line in sql('\n'.join(commands)): if line.startswith('__step_'): chunks.append([]) else: chunks[-1].append(line) assert chunks == [s['expected'] for s in steps], chunks record['articleSteps'] = [dict(s, actual=a) for s, a in zip(steps, chunks)] final = next(s['sql'] for s in steps if s['id'] == 'final') main = [(7, 1, '2024-02-28', 3), (7, 2, '2024-02-29', 0), (42, 1, '2024-02-28', 1), (42, 1, '2024-03-01', 4), (42, 2, '2024-03-02', 2), (90, 3, '2024-12-31', 5)] cases = [main, []] for start in [date(2024, 2, 27), date(2026, 12, 30)]: slots = [(p, (start + timedelta(days=i)).isoformat()) for p in [7, 42] for i in range(4)] for bits in itertools.product([False, True], repeat=len(slots)): cases.append([(p, (i % 3) + 1, day, i % 2) for i, ((p, day), enabled) in enumerate(zip(slots, bits)) if enabled]) for days in [('2024-02-28', '2024-02-29'), ('2024-02-29', '2024-03-01'), ('2023-02-28', '2023-03-01'), ('2026-12-31', '2027-01-01')]: cases.append([(99, i + 1, day, 0) for i, day in enumerate(days)]) commands = ['USE blog_retention;'] for index, rows in enumerate(cases): commands.extend(['DELETE FROM Activity;', insert(rows), f"SELECT '__case_{index}__';", final]) actual = [] for line in sql('\n'.join(commands)): if line.startswith('__case_'): actual.append([]) else: actual[-1].append(line) assert actual == [oracle(rows) for rows in cases] record['cases'] = len(cases) record['emptyTableResult'] = actual[1] any_pair = '''SELECT ROUND(COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) FROM Activity a JOIN Activity b ON a.player_id=b.player_id AND b.event_date=DATE_ADD(a.event_date, INTERVAL 1 DAY);''' mutants = { 'any-consecutive-days': any_pair, 'last-login-instead-of-first': final.replace('MIN(event_date)', 'MAX(event_date)'), 'divide-by-log-rows': final.replace('COUNT(*)', '(SELECT COUNT(*) FROM Activity)'), 'drop-non-returners': final.replace('LEFT JOIN', 'INNER JOIN'), 'check-first-day-itself': final.replace('INTERVAL 1 DAY', 'INTERVAL 0 DAY'), 'require-positive-games': final.replace('INTERVAL 1 DAY)', 'INTERVAL 1 DAY) AND a.games_played > 0'), 'increment-only-day-of-month': final.replace( 'a.event_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)', 'YEAR(a.event_date)=YEAR(f.first_date) AND MONTH(a.event_date)=MONTH(f.first_date) ' 'AND DAY(a.event_date)=DAY(f.first_date)+1') } record['mutants'] = [] for name, query in mutants.items(): assert query != final witness = main if name != 'increment-only-day-of-month' else cases[-1] rows = sql('USE blog_retention; DELETE FROM Activity;\n' + insert(witness) + query) assert rows != oracle(witness), name record['mutants'].append({'name': name, 'actual': rows, 'expected': oracle(witness), 'rejected': True}) record['passed'] = True finally: server.terminate() try: server.wait(timeout=30) except subprocess.TimeoutExpired: server.kill() server.wait(timeout=5) record['serverStopped'] = server.poll() is not None record['socketRemoved'] = not sock.exists() (run / 'results.json').write_text(json.dumps(record, indent=2)) print('RESULTS=' + str(run / 'results.json')) assert record['passed'] and record['serverStopped'] and record['socketRemoved'] print('RETENTION_CHECKS_PASSED'))
