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