CODEWITHSUNDEEP
sqloracle-sqldeveloper
user6522753
user6522753

Reputation:

SQL to retrieve last 3 records if they match a certain criteria

I'm looking for the following

TBL_TRANSACTION
(
  ACCOUNT ID
  TRANSACTION_DETAILS
  TRANSACTION_DATE
  TRANSACTION_TYPE
)

ACC_ID TRANS_DTLS TRANS_DATE TRANS_TYPE     
1001   Petrol     01-Sept-2015 Cash    
1001   Petrol     01-July-2015 Cash    
1001   Fruit      01-May-2015 Cash    
1001   Biscuits   01-Feb-2015 Cash    
1002   Cereal     01-Sept-2015 Cash    
1002   Soft Drinks 01-July-2015 Card    
1002   Water      01-May-2015 Cash    
1002   Water      01-Feb-2015 Card    
1003   Milk       01-Sept-2015 Card    
1003   Petrol     01-July-2015 Cash    
1003   Cereal     01-May-2015 Cash    
1003   Biscuits   01-Feb-2015 Cash

Basically, I want to be able to pull back the records of the account IF the last 3 transactions have ALL been paid for by Cash.

Regarding the data above, Account 1001 would be returned as the last 3 transactions have all been Cash, where are 1002 and 1003 would not as at least 1 of the last 3 transactions are card payments.

Upvotes: 1

Views: 225

Answers (3)

paparazzo
paparazzo

Reputation: 45096

with cte as
( select ACCOUNT_ID
       , TRANSACTION_DETAILS
       , TRANSACTION_DATE
       , TRANSACTION_TYPE 
       , row_number() over (partition by ACCOUNT_ID order by TRANSACTION_DATE desc) as rn 
  from TBL_TRANSACTION 
)

select cm.* 
from cte cm 
where cm.rn <= 3 
-- and cm.TRANSACTION_TYPE = 'cash' 
and cm.ACCOUNT_ID not in ( select cx.ACCOUNT_ID
                           from cte cx 
                           where rn <= 3 
                           and TRANSACTION_TYPE <> 'cash' )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You can get the last three accounts using row_number(). You can get the last three cash account using row_number() as well, with a different partitioning clause. If the values are the same for the first three, then the last three are cash.

select t.*
from (select t.*,
             sum(case when seqnum = seqnum_type then 1 else 0 end) over (partition by acc_id) as num_in_sequence
      from (select t.*,
                   row_number() over (partition by acc_id order by transaction_date desc) as seqnum,
                   row_number() over (partition by acc_id, trans_type order by transaction_date desc) as seqnum_type
            from tbl_transaction t
           ) t
      where trans_type = 'cash'
     )
where num_in_sequence >= 3 and seqnum <= 3;

Another method compares the transaction types for the last three records:

select t.*
from (select t.*,
             min(trans_type) over (partition by acc_d) as min_trans_type,
             max(trans_type) over (partition by acc_d) as max_trans_type
      from (select t.*,
                   row_number() over (partition by acc_id order by transaction_date desc) as seqnum
            from tbl_transaction t
           ) t
      where seqnum <= 3
     )
where min_trans_type = 'cash' and max_trans_type = 'cash';

Upvotes: 0

sagi
sagi

Reputation: 40481

You didn't tag your RDBMS, so for ANSI-SQL you can use ROW_NUMBER() :

SELECT tr.* FROM tbl_transaction tr
INNER JOIN(
    SELECT t.acc_id FROM (
        SELECT t.acc_id,t.trans_type,
               ROW_NUMBER() OVER(PARTITION BY t.acc_id ORDER BY t.trans_date DESC) as rnk
        FROM tbl_transaction t) tt
    WHERE tt.rnk < 4 and t.trans_type = 'Cash'
    GROUP BY tt.acc_id having count(*) = 3) s
 ON(t.acc_id = s.acc_id)

Upvotes: 1

Related Questions