查找在注册后特定时间内登录的用户。

Question

我试图弄清楚如何处理以下问题：

我有registrations桌

+---------+---------------------+
| user_id |      reg_date       |
+---------+---------------------+
| a       | 2018-11-01 20:47:46 |
| b       | 2018-11-02 21:07:15 |
| c       | 2018-11-03 05:24:31 |
+---------+---------------------+

和使用logins的表

+---------+---------------------+
| user_id |     login_date      |
+---------+---------------------+
| a       | 2018-11-01 21:30:46 |
| a       | 2018-11-01 21:35:15 |
| a       | 2018-11-01 22:22:22 |
| ...     |                     |
+---------+---------------------+

因此，我需要让用户在注册后的第一天(注册后24到48小时)至少登录一次，并在第二天显示user_id、register_date和登录的最高值。

最后我得到了以下解决方案：

WITH a 
     AS (SELECT registrations.user_id, 
                registrations.reg_date, 
                logins.login_date, 
                Row_number() 
                  OVER( 
                    partition BY registrations.user_id 
                    ORDER BY logins.login_date DESC) row_num 
         FROM   registrations 
                INNER JOIN logins 
                        ON registrations.user_id = logins.user_id 
         WHERE  logins.login_date BETWEEN Hours_add(registrations.reg_date, 24) 
                                          AND 
                                          Hours_add(registrations.reg_date, 48)) 
SELECT * 
FROM   a 
WHERE  row_num = 1 

但我不确定我的解决方案，虽然它看起来很好。你能检查一下并提出更简单的计算方法吗？

Answer 1

这应该能行

SELECT r.user_id, 
       r.reg_date, 
       Max(l.login_date), 
       Count(l.login_date) 
FROM   registrations r, 
       logins l 
WHERE  r.user_id = l.user_id 
       AND l.login_date BETWEEN ( r.reg_date + interval '1' day ) AND ( 
                                r.reg_date + interval '2' day )
group by l.login_date        
having Count(l.login_date)>=1 
;