Query to identify people exceeding a particular amount within an interval of 6 months

oracle

I have tried searching in Google but am just not finding the right post.
I have this table with fields trans_date, CID (which is customerid), and amount.

How do I find the CID exceeding an amount of say $1000 within any time period of 6 months or 180 days (eg. Jan-June or Feb-July, or November 2014-April 2015 (periods overlapping years too)) starting from the beginning of time?

trans_date             CID        amount
22-Jun-2015            123        550
24-Aug-2015            999        200    
15-Aug-2015            123        500
24-nov-2014            321        200 
08-Jan-2015            321        900
24-oct-2015            999        200

In the result CID 123 and 321 should be the output both exceeding the total amount 1000 in a span of 6 months.

Any response would be highly appreciated.

Best Answer

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period:

select cid,
       sum(amount)
from transfer
where trans_date >= date '2014-11-01' 
  and trans_date < date '2015-06-01'
group by cid
having sum(amount) >= 1000;

But this is not what you want.

You want a "rolling running total" that covers the last 6 month relative to the the trans_date value of each row. This can easily be achieved using a window function (aka "analytical function")

select trans_date, 
       cid, 
       amount,
       sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
from transfer
order by trans_date;

The "trick" is the "range between ..." part: it will only evaluate the running total for the "last 6 month" relative to the month of the "current row"

The above returns the following result for your sample data:

TRANS_DATE          | CID | AMOUNT | RUNNING_TOTAL
--------------------+-----+--------+--------------
2014-11-24 00:00:00 | 321 |    200 |           200
2015-01-08 00:00:00 | 321 |    900 |          1100
2015-06-22 00:00:00 | 123 |    550 |           550
2015-08-15 00:00:00 | 123 |    500 |          1050
2015-08-24 00:00:00 | 999 |    200 |           200
2015-10-24 00:00:00 | 999 |    200 |           400

Now to get the customers you are interested in, you can wrap this query into a derived table and then get only those CIDs that have at least one running total that exceeds 1000:

select distinct cid
from (
  select trans_date, 
         cid, 
         amount,
         sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
  from transfer
) t
where running_total > 1000;

The distinct is necessary because a customer can have more than one "6 month period" where the total amount exceeds 1000.

This is probably not the most efficient query though - but currently I can't think of anything better.

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/1e26f/2

Related Question