Siva Academy

Youtube.com/SivaAcademy  Oracle SQL  |  PLSQL  |  SQL PLSQL Interview Questions  |  PLSQL Scenario based Interview Questions  |  Subscriber Questions & Answers

  |  SQL practical Question  |  SQL Performance Tuning  |  New Features  |  

  Trigger     |    View  

Monday, August 12, 2019

Find the last two recharge amount for each customer

Find the last two recharge amount for each customer ( Subscriber Comments Reply Video 19 Get the last two recharged amount)



Data Setup Scripts:

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,
       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;

No comments:

Post a Comment