SQL：根据用户注册表和事件日志计算第一天保留率

我需要按用户注册日期计算第一天的留存率。第 1 天留存率定义为注册日期 1 天后返回的用户数量除以注册日期当天注册的用户数量。

这是用户表

CREATE TABLE registration (
  user_id SERIAL PRIMARY KEY,
  user_name VARCHAR(255) NOT NULL,
  registrationDate TIMESTAMP NOT NULL
);

INSERT INTO registration (user_id, user_name, registrationDate)
VALUES
  (0, 'John', '2018-01-01 00:01:00'),
  (1, 'David', '2018-01-01 00:04:30'),
  (2, 'Cassy', '2018-01-02 10:00:00'),
  (3, 'Winka', '2018-01-02 14:30:00')
;

CREATE TABLE log (
  user_id INTEGER,
  eventDate TIMESTAMP
);

INSERT INTO log (user_id, eventDate)
VALUES
  (0, '2018-01-01 01:00:00'),
  (0, '2018-01-02 04:00:00'),
  (0, '2018-01-04 06:00:00'),
  (1, '2018-01-01 00:30:00'),
  (3, '2018-01-02 14:40:00'),
  (3, '2018-01-04 12:20:00'),
  (3, '2018-01-06 13:30:00'),
  (2, '2018-01-12 10:10:00'),
  (2, '2018-01-13 09:00:00')

我尝试将注册表连接到日志表，这样我就可以比较日期差异。

select registration.user_id, registrationDate, log.eventDate, 
(log.eventDate - registration.registrationDate) as datediff 
from log left join registration ON log.user_id = registration.user_id

我认为我需要以某种方式执行以下任务。

1. select the users with datediff = 1 and count them.
   • 我添加了一个 where 语句，但收到错误消息“datediff 不存在位置”

where datediff = 1

2. do the Group By registrationDate.
   • 这也给了我一个错误：“错误：列“registration.user_id”必须出现在 GROUP BY 子句中或在聚合函数中使用”

我是 SQL 新手，在解决问题时学习它。任何帮助/建议将不胜感激

预期结果应返回一个包含两列（注册日期和保留时间）的表，其中包含任何用户注册的每个日期的行。

第 1 天留存率定义为注册日期 1 天后返回的用户数量除以注册日期当天注册的用户数量。

这将定义解释为基于日历日。我将其表达为：

用户注册后第二天回来的比例是多少？

我认为这是最简单的方法：

select count(distinct l.user_id) * 1.0 / count(distinct r.user_id)
from registration r left join
     log l
     on l.user_id = r.user_id and
        l.eventDate::date = r.registrationDate::date + interval '1 day';

The count(distinct)仅当一天内可能发生多个事件时才需要。

是一个数据库小提琴。

我不确定这个定义是否 100% 有用。如果您有其他定义，我建议您询问new问题，具有适当的样本数据和期望的结果.