Find the last two recharge amount for each customer

 

 

 

 

 

CREATE TABLE recharge_detail (

    recharge_date     DATE,

    cust_name         VARCHAR2(100),

    recharge_amount   NUMBER

);

 

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Ragu',100);

insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Ragu',150);

insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Ragu',120);

insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Ragu',170);

 

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Ravi',299);

insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Ravi',399);

insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Ravi',150);

insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Ravi',199);

 

insert into recharge_detail values( to_date('01/01/2019','DD/MM/YYYY'), 'Siva',100);

insert into recharge_detail values( to_date('01/02/2019','DD/MM/YYYY'), 'Siva',200);

insert into recharge_detail values( to_date('01/03/2019','DD/MM/YYYY'), 'Siva',400);

insert into recharge_detail values( to_date('01/04/2019','DD/MM/YYYY'), 'Siva',200);

 

commit;

 

select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,

rank() over(partition by CUST_NAME order by RECHARGE_DATE desc) from recharge_detail

order by CUST_NAME,RECHARGE_DATE ;

 

select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,

rank() over(partition by CUST_NAME order by RECHARGE_DATE desc) from recharge_detail

order by CUST_NAME,RECHARGE_DATE desc;

 

select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,

       decode(rank() over(partition by cust_name order by Recharge_Date desc ),1,RECHARGE_AMOUNT,2,RECHARGE_AMOUNT) last_two_recharge_amount

from recharge_detail

order by CUST_NAME,RECHARGE_DATE;

-------------------------------------------------------------------------------------------------------------

select RECHARGE_DATE, CUST_NAME, RECHARGE_AMOUNT,

       case when rank() over(partition by cust_name order by Recharge_Date desc ) in(1,2) then RECHARGE_AMOUNT end

from recharge_detail

order by CUST_NAME,RECHARGE_DATE;

-------------------------------------------------------------------------------------------------------------

SELECT

    cust_name,

    MAX(DECODE(r,1,recharge_amount) ) last_recharge,

    MAX(DECODE(r,2,recharge_amount) ) last_2nd_recharge

FROM

    (

        SELECT

            cust_name,

            recharge_amount,

            RANK() OVER(PARTITION BY cust_name ORDER BY recharge_date DESC ) r

        FROM

            recharge_detail

    )

GROUP BY

    cust_name;