Find records with 3 or more consecutive records with same value

1
votes

I have a list of dated transactions in a table containing a date, customer number, transaction type and value. I need to return all the transactions of a given customer if that customer has 3 or more consecutive 'cash' transactions in a row when sorted by date.

So in the sample data below I want to return all the transactions (including credit ones) for customers 1 and 3 as both customers has 3 or more cash transactions in a row. Customer 2 is ignored because even though they had more than 3 cash transactions, they weren't consecutive.

╔════════════╦════════════╦═══════════╦═══════╗
║  Customer  ║    Date    ║ TransType ║ Value ║
╠════════════╬════════════╬═══════════╬═══════╣
║ Customer 1 ║ 1/01/2015  ║ cash      ║ 23.00 ║
║ Customer 1 ║ 2/01/2015  ║ cash      ║ 24.00 ║
║ Customer 2 ║ 2/01/2015  ║ cash      ║ 28.00 ║
║ Customer 2 ║ 4/01/2015  ║ credit    ║ 29.00 ║
║ Customer 3 ║ 5/01/2015  ║ credit    ║ 27.00 ║
║ Customer 2 ║ 6/01/2015  ║ cash      ║ 23.00 ║
║ Customer 2 ║ 8/01/2015  ║ credit    ║ 24.00 ║
║ Customer 3 ║ 9/01/2015  ║ cash      ║ 28.00 ║
║ Customer 3 ║ 13/01/2015 ║ cash      ║ 29.00 ║
║ Customer 1 ║ 15/01/2015 ║ cash      ║ 25.00 ║
║ Customer 1 ║ 17/01/2015 ║ credit    ║ 26.00 ║
║ Customer 3 ║ 18/01/2015 ║ cash      ║ 23.00 ║
║ Customer 1 ║ 20/01/2015 ║ cash      ║ 27.00 ║
║ Customer 3 ║ 20/01/2015 ║ credit    ║ 24.00 ║
║ Customer 2 ║ 21/01/2015 ║ cash      ║ 25.00 ║
║ Customer 3 ║ 22/01/2015 ║ credit    ║ 25.00 ║
║ Customer 2 ║ 23/01/2015 ║ cash      ║ 26.00 ║
╚════════════╩════════════╩═══════════╩═══════╝
2
sqlsql-server-2008
What exactly do you mean by consecutive? Monday, Tuesday, Wednesday kinda consecutive? I cant see any pattern in your table. Also I dont think SQL queries alone would cut it. Try running a PHP script where you SELECT all customers from the start of the database and check if the same customers can be SELECTed on the next "consecutive" day and the next to next day.Then increment the start date by one and check for next two "consecutive" days. – Souradeep Nanda
The list above needs to be sorted by customer and then by date. Once that is done, if 3 or more transactions in a row per customer are 'cash' transactions then I want to see all the transactions for that customer. – Goolsy

2 Answers

2
votes

You can use a trick to enumerate the "cash" transactions. This trick is a difference of row numbers and it is very useful:

select t.*
from (select t.*, count(*) over (partition by grp, customerid, transtype) as cnt
      from (select t.*,
                   (row_number() over (partition by customerid order by date) -
                    row_number() over (partition by customerid, transtype order by date)
                   ) as grp
            from t
           ) t
      where transtype = 'cash'
     ) t
where cnt >= 3;

This returns the customers and the start date. If you want to return the actual transactions, you can use an addition level of window functions:

select customerid, min(date) as start_date, sum(value) as sumvalue
from (select t.*,
             (row_number() over (partition by customerid order by date) -
              row_number() over (partition by customerid, transtype order by date)
             ) as grp
      from t
     ) t
where transtype = 'cash'
group by grp, transtype, customerid
having count(*) >= 3;
2
votes

So to get the customers that have at least three consecutive cash transactions you can use a self join and for each row connect the row before and after and test if all three are transtype cash.

The query used as the first common table expression numbers all rows partitioned by customer so we have a suitable column to connect them by. The connection is then made in the second common table expression and the result from that is fed into the final query. The query could be shortened, but I left it a bit longer for clarity.

with cte as (
    select *, r = row_number() over (partition by customer order by date) 
    from table1 -- this is your source table
), cte2 as (
    select t1.customer 
    from cte t1
    join cte t2 on t1.customer = t2.customer and (t1.r = t2.r-1 or t1.r = t2.r+1)
    where t1.transtype = 'cash' and t2.transtype = 'cash'
    group by t1.customer
    having count(*) >= 3
)

select * from Table1 -- this is your source table
where Customer in (select Customer from cte2)
order by customer, date;

With your sample data this would return all rows for customers 1 and 3.

Sample SQL Fiddle