Earliest date of the latest ID

dazz123

Hi,

I am trying to get the earliest start time of the latest plan that the person took. In the following, the latest plan is P1, and the earliest start date of it is 06-Feb-2015 . It should therefore return P1 , 06-Feb-2015

ID Date Plan Bundle
100 03-Jan-2014 P1 12
100 13-Oct-2014 P2 07
100 21-Dec-2014 P2 09
100 06-Feb-2015 P1 11
100 06-Nov-2015 P1 13
100 18-Apr-2016 P1 14

Ive tried a few ways to get it, but Im not getting thru. Appreciate any assistance pleaseee

Thanks!

Jim_Knicely

Why wouldn't the Date be 2014-01-03? That's the earliest date for P1.

dbadmin=> select * from test order by Date;
 ID  |    Date    | Plan | Bundle
-----+------------+------+--------
 100 | 2014-01-03 | P1   | 12
 100 | 2014-10-13 | P2   | 07
 100 | 2014-12-21 | P2   | 09
 100 | 2015-02-06 | P1   | 11
 100 | 2015-11-06 | P1   | 13
 100 | 2016-04-18 | P1   | 14
(6 rows)

dbadmin=> select Date, Plan from (select first_value(Date) over (partition by ID, Plan order by Date) Date, Plan, last_value(Plan) over (partition by ID order by Date range between unbounded preceding and unbounded following) last_plan from test) foo where Plan = last_plan limit 1;
    Date    | Plan
------------+------
 2014-01-03 | P1
(1 row)

RaghavA

Hi,

It can be easily written as:

Select foo.plan,foo.date from ( select plan, date from test where plan = ( select min(plan) from test ) foo order by 2 limit 1;

Jim_Knicely

@RaghavA I thought that also. In fact I was going to suggest this:

 dbadmin=> select Plan, Min(date) from test group by ID, Plan order by Plan limit 1;
  Plan |    Min
 ------+------------
  P1   | 2014-01-03
 (1 row)

But the "latest plan" isn't the first plan alphabetically I think it is just a coincidence in the sample data.

RaghavA

@Jim_Knicely yes, that's correct. I just wrote it as per the given dataset.

peterjansens

I think what we're looking for is the start-date and end-date for the latest 'session' of plan P1. Customer started at plan P1, then went to P2 stayed on P2 and went back to P1. So something like this:

min     |    max     | Plan

------------+------------+------
2015-02-06 | 2016-04-18 | P1
(1 row)

WITH mydata AS
(SELECT
ID ,Date ,Plan ,Bundle
, CONDITIONAL_TRUE_EVENT(Plan <> LAG(Plan) )
OVER (PARTITION BY ID ORDER BY Date)
AS session_id
FROM mytb
)
select min(Date), max(Date), Plan
from mydata
where session_id = (select session_id from mydata where Date = (select max(Date) from mydata))
group by Plan
;

....hope this makes sense!

Jim_Knicely

@peterjansens: That makes sense! I guess there are a million ways to write the SQL

dbadmin=> select Plan, Date from (select Plan, Date, conditional_change_event(Plan) over (partition by id order by Date) rn from test order by rn desc, Date limit 1) foo;
 Plan |    Date
------+------------
 P1   | 2015-02-06
(1 row)

peterjansens

Nice, the 'limit 1' function has always lived in my mind as 'something for testing', but there is no reason to limit (pun intended) it as such.

dazz123

Thanks everyone! I got thru with your suggestions. I used :

dbadmin=> select Plan, Date from (select Plan, Date, conditional_change_event(Plan) over (partition by id order by Date) rn from test order by rn desc, Date limit 1) foo;

I was looking for the current Plan they are on, which is P1, and when they started/switched to it ..which is 2015-02-06
Thanks so much!!