I have a login log from which I want to derive the first time a user logged in through a month plus n days.
For a single period this is of course very easy, but I want an otput like:
User-ID, Period Start, Period End, Min Log in time
1, 1/1/2016,2/10/2016,1/12/2016 03:24:12
1,2/1/2016,3/10/2016,2/5/2016 12:43:40
This means the first period goes from January 1 to February 10 and the second period from February 1 to March 10.
Input log is just User-ID and Log in time.
Best regards!
/P
Overlapping time intervals
Moderator: NorbertKrupa
Re: Overlapping time intervals
Something like this?
Code: Select all
create table activity(userid integer, login_ts timestamp);
insert into activity values(1, '1/12/2016 03:24:12');
insert into activity values(1, '2/5/2016 12:43:40');
insert into activity values(1, '1/13/2016 09:00');
insert into activity values(1, '1/23/2016 09:00');
insert into activity values(1, '1/31/2016 09:00');
insert into activity values(1, '2/05/2016 09:00');
insert into activity values(1, '2/15/2016 09:00');
insert into activity values(1, '3/05/2016 09:00');
insert into activity values(2, '1/10/2016 09:00');
insert into activity values(2, '1/20/2016 09:00');
insert into activity values(2, '1/30/2016 09:00');
insert into activity values(2, '2/15/2016 09:00');
commit;
create table dates(period_start date);
insert into dates values ('2016-01-01');
insert into dates values ('2016-02-01');
insert into dates values ('2016-03-01');
commit;
select
userid,
min(period_start) as "Period Start",
max(period_end) as "Period End",
min(login_ts) as "First Login Time",
max(login_ts) as "Last Login Time"
from activity a
join
(
select period_start, date(add_months(period_start, 1) + '12 days'::interval) as period_end from dates
) q
on login_ts between period_start and period_end
group by userid, period_start
order by userid, period_start;
userid | Period Start | Period End | First Login Time | Last Login Time
--------+--------------+------------+---------------------+---------------------
1 | 2016-01-01 | 2016-02-13 | 2016-01-12 03:24:12 | 2016-02-05 12:43:40
1 | 2016-02-01 | 2016-03-13 | 2016-02-05 09:00:00 | 2016-03-05 09:00:00
1 | 2016-03-01 | 2016-04-13 | 2016-03-05 09:00:00 | 2016-03-05 09:00:00
2 | 2016-01-01 | 2016-02-13 | 2016-01-10 09:00:00 | 2016-01-30 09:00:00
2 | 2016-02-01 | 2016-03-13 | 2016-02-15 09:00:00 | 2016-02-15 09:00:00
(5 rows)
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Overlapping time intervals
Thanks Sharon, problem solved! ![Shocked :shock:](./images/smilies/icon_eek.gif)
![Shocked :shock:](./images/smilies/icon_eek.gif)