Earliest date of the latest ID

dazz123dazz123 Registered User

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!

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited July 2017

    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)
    
  • RaghavARaghavA Registered User
    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    @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.

  • RaghavARaghavA Registered User
    @Jim_Knicely yes, that's correct. I just wrote it as per the given dataset.
  • peterjansenspeterjansens Employee, Registered User

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited July 2017

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

    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)
    
  • peterjansenspeterjansens Employee, Registered User

    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.

  • dazz123dazz123 Registered User

    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!!
    :)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file