2

This is a much simplified table but the process should be the same. I want to calculate the amount of days between logins. Here is the table set-up query.

create table tester (user_id int, login_day date);

insert into tester (user_id,login_day) 
values 
 (1,'2013-10-02'),
 (1,'2013-10-05'),
 (2,'2013-10-03'),
 (2,'2013-10-04'),
 (2,'2013-10-07');

To give this:

user_id; login_day
     1;  "2013-10-02"
     1;  "2013-10-05"
     2;  "2013-10-03"
     2;  "2013-10-04"
     2;  "2013-10-07"

The results table should be like this:

user_id; login_day; tau
     1;"2013-10-02"; 2
     1;"2013-10-05"; 1 --see edit
     2;"2013-10-03"; 0
     2;"2013-10-04"; 2 
     2;"2013-10-07"; 0

Where tau is the difference in days-1. User_id(1) logged in on the 2nd and the 5th. So their tau value is 2 since it was two days between logins.

User_id(2) has a value of 0 since they logged in on consecutive days.

The 7th gets a 0 since no time has passed. Could also be labeled as -1 if it's easier.

Thanks alot for the help. Much appreciated.

EDIT Clarification. User_id(1), 2013-10-05 has a tau value of 1 because they haven't logged back in when treating the current day as the 7th. e.g So if someone hasn't logged back in, their tau value keeps increasing. So since user_id=1 last logged in on the 5th, they currently have a tau value of 1, and that value will keep increasing by 1 for everyday they don't log back in. Thanks again-especially to a_horse_with_no_name and Linger for the quick responses and helping me clarify the question

Improve this question
3
  • I don't understand your edit. You were first saying that you want the number of days in-between the login date and the previous login date. My answers shows you how to get that. Now you want the days between the login date and the current date. But, your results table still reflects your first request. So what do you want, the days in-between logins or the difference of login date and current date? Can you fix you results table? Commented Oct 7, 2013 at 19:32
  • @ Linger. Your answer was correct and showed what I wanted. I want to measure the days between logins. So if someone hasn't logged back in, their tau value keeps increasing. So since user_id=1 last logged in on the 5th, they currently have a tau value of 1, and that value will keep increasing by 1 for everyday they don't log back in. Commented Oct 7, 2013 at 19:41
  • Ok, if my answer was what you are looking for and the best answer, can you mark it as the accepted answer? Commented Oct 7, 2013 at 19:51

2 Answers 2

Reset to default
3

That gives you nearly the correct answer:

select user_id, 
       login_day,
       lead(login_day) over (partition by user_id order by user_id) - login_day - 1 as tau
from tester
order by user_id, login_day;

Output:

user_id | login_day  | tau   
--------+------------+-------
1       | 2013-10-02 | 2     
1       | 2013-10-05 | (null)
2       | 2013-10-03 | 0     
2       | 2013-10-04 | 2     
2       | 2013-10-07 | (null)

As I don't understand the rule, why for user_id=1 the "last" login generates 2 but for user_id 2 the last login generates 0 I'm not quite sure how to fix the wrong display for the second login of user_id = 1

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Sorry I forgot to state that I was treating the 7th as the current day day. Therefore the user hadn't logged in for 2 days or rather 1 one day.
Tau is essentially-'days since last login'. Thanks
@AndrewEscobedo: then I don't understand why the first login of user_id = 1 gets a 2 but the first one for user_id = 2 gets a 0. As both are the "first" login, there is no such thing as the number of dasys "since the last"
Sorry for lack of clarification again. User_id=1 created the account on the 2nd. Next time they logged in was the 5th=two day difference. User_id=2 created the account on the 3rd and logged back in the 4th==consecutive login days results in 0. User_id=1 has a tau of 1(shown as 2) since they haven't logged back in for a full day when treating the current day as the 7th.If they log in on the 8th then their tau becomes a 2.
2

If you don't mind Null in your results then you can use(SAMPLE):

SELECT m.user_id, m.login_day, m.login_day - 
(
   SELECT MAX(login_day) 
   FROM tester AS s 
   WHERE m.user_id = s.user_id 
   AND s.login_day < m.login_day
) - 1 AS DaysInBetween
FROM tester AS m

If you want to check for Null and assign 0 then use the following (SAMPLE):

SELECT TopL.user_id, TopL.login_day,
       COALESCE(TopL.DaysInBetween, 0) As TotalD
FROM   (
         SELECT m.user_id, m.login_day, 
           m.login_day - 
           (
             SELECT MAX(login_day)
             FROM tester AS s 
             WHERE m.user_id = s.user_id 
             AND s.login_day < m.login_day
           ) - 1 AS DaysInBetween
         FROM tester AS m
       ) AS TopL
ORDER BY user_id, 
         login_day

Here is another query to consider (SAMPLE):

SELECT TopL.user_id, TopL.login_day,
       CASE (COALESCE(TopL.DaysInBetween, 0)) WHEN '-1'
          THEN '0' ELSE COALESCE(TopL.DaysInBetween, 0) END
          As TotalD
FROM (
       SELECT m.user_id, m.login_day, 
         COALESCE(
         (
           SELECT MIN(login_day)
           FROM tester AS s 
           WHERE m.user_id = s.user_id 
           AND s.login_day > m.login_day
         )- m.login_day - 1, current_date - m.login_day-1)
         AS DaysInBetween
       FROM tester AS m
       ) AS TopL
ORDER BY user_id, 
         login_day
Improve this answer

1 Comment

Thanks for this - worked great for my needs. One note: in postgres 9.6 I got an error ERROR: operator does not exist: interval - integer. Changing - 1 AS DaysInBetween to - interval '1 day' AS DaysInBetween resolved the issue for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.