Comments

• Abhishek_Rana Vertica Employee Employee

  February 2014

  We can use RANK() OVER Partition for this :
  
  Suppose you have following data in your table:
  =================================
  
  dbadmin=> select * from Transaction order by 1,2;
  
   clientid |  buy_date  | amount 
   ----------+------------+--------         
  1 | 2013-01-01 |    200         
  1 | 2013-01-04 |    300         
  1 | 2013-01-06 |    400         
  2 | 2013-01-06 |    400         
  2 | 2013-02-06 |    500         
  2 | 2013-02-07 |    600         
  3 | 2013-02-03 |    600         
  3 | 2013-02-04 |    600         
  3 | 2013-02-05 |   1200
  
  You can get get all clients second purchases as below:
  =======================================
  
  dbadmin=> select * from ( 
                      SELECT clientid, buy_date,amount, rank() 
                      OVER(PARTITION BY clientid 
                      ORDER BY buy_date) As RANKING 
                      FROM Transaction 
                                          ) as Trans where Trans.RANKING=2 
                      order by 1;
  
  clientid |  buy_date  | amount | RANKING
  ----------+------------+--------+---------
  1 | 2013-01-04 |    300 |       2         
  2 | 2013-02-06 |    500 |       2       
   3 | 2013-02-04 |    600 |      2
  
  (3 rows)
  
  Regards'
  
  Abhishek
  
  

  0
• Navin_C Vertica Customer ✭

  February 2014

  Adding to the solution
  
  if you want to know the second purchase of a client for a every day basis.
  
    select * from ( 
                      SELECT clientid, buy_date,amount, rank() 
                      OVER(PARTITION BY clientid,buy_date 
                      ORDER BY buy_date) As RANKING 
                      FROM Transaction 
                                          ) as Trans where Trans.RANKING=2 
                      order by 1;
  
  Hope this helps
  

  0