Oracle first and last observation over multiple windows

Question

I have a problem with a query in Oracle.

My table contains all of the loan applications from last year. Some of the customers have more than one application. I want to aggregate those applications as follows:

For each customer, I want to find his first application (let's call it A) in the last year and then I want to find out what was the last application in 30 days interval, counting from the first application (say B is the last one). Next, I need to find the application following B and again find for it the last one in 30 days interval, as in the previous step. What I want as the result is the table with the latest and earliest applications on each customer's interval. It is also possible that the first one is the same as the last one.

How could I do this in Oracle without plsql? Is this possible? Should I use cumulative sums of time intervals for it? (but then the starting point for each sum depends on the counted sum..)

Let's say the table has a following form:

application_id (unique) | customer_id (not unique) | create_date
1                         1                          2017-01-02 <- first
2                         1                          2017-01-10 <- middle
3                         1                          2017-01-30 <- last
4                         1                          2017-05-02 <- first and last
5                         1                          2017-06-02 <- first
6                         1                          2017-06-30 <- middle
7                         1                          2017-06-30 <- middle
8                         1                          2017-07-01 <- last

What I expect is:

application_id (unique) | customer_id (not unique) | create_date
1                         1                          2017-01-02 <- first
3                         1                          2017-01-30 <- last
4                         1                          2017-05-02 <- first and last
5                         1                          2017-06-02 <- first
8                         1                          2017-07-01 <- last

Thanks in advance for help.

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( application_id, customer_id, create_date ) AS
SELECT 1, 1, DATE '2017-01-02' FROM DUAL UNION ALL -- <- first
SELECT 2, 1, DATE '2017-01-10' FROM DUAL UNION ALL -- <- middle
SELECT 3, 1, DATE '2017-01-30' FROM DUAL UNION ALL -- <- last
SELECT 4, 1, DATE '2017-05-02' FROM DUAL UNION ALL -- <- first and last
SELECT 5, 1, DATE '2017-06-02' FROM DUAL UNION ALL -- <- first
SELECT 6, 1, DATE '2017-06-30' FROM DUAL UNION ALL -- <- middle
SELECT 7, 1, DATE '2017-06-30' FROM DUAL UNION ALL -- <- middle
SELECT 8, 1, DATE '2017-07-01' FROM DUAL           -- <- last

Query 1:

WITH data ( application_id, customer_id, create_date, first_date, grp ) AS (
  SELECT t.application_id,
         t.customer_id,
         t.create_date,
         t.create_date,
         1
  FROM table_name t
  WHERE  application_id = 1
UNION ALL
  SELECT t.application_id,
         t.customer_id,
         t.create_date,
         CASE WHEN t.create_date <= d.first_date + INTERVAL '30' DAY
         THEN d.first_date
         ELSE t.create_date
         END,
         CASE WHEN t.create_date <= d.first_date + INTERVAL '30' DAY
         THEN grp
         ELSE grp + 1
         END
  FROM   data d
         INNER JOIN table_name t
         ON (  d.customer_id        = t.customer_id
           AND d.application_id + 1 = t.application_id )
)
SELECT application_id,
       customer_id,
       create_date,
       grp
FROM   (
  SELECT d.*,
         ROW_NUMBER() OVER ( PARTITION BY customer_id, grp ORDER BY create_date ASC  ) AS rn_a,
         ROW_NUMBER() OVER ( PARTITION BY customer_id, grp ORDER BY create_date DESC ) AS rn_d
  FROM   data d
)
WHERE rn_a = 1
OR    rn_d = 1

Results:

| APPLICATION_ID | CUSTOMER_ID |          CREATE_DATE | GRP |
|----------------|-------------|----------------------|-----|
|              1 |           1 | 2017-01-02T00:00:00Z |   1 |
|              3 |           1 | 2017-01-30T00:00:00Z |   1 |
|              4 |           1 | 2017-05-02T00:00:00Z |   2 |
|              5 |           1 | 2017-06-02T00:00:00Z |   3 |
|              8 |           1 | 2017-07-01T00:00:00Z |   3 |