A example using analytical function

I am sure using SQL analytical function, the following can be achieved using a single query:

Date_value | Cust_id | Customer_tenue | avg_bal 
 
01-aug-09 | 111 | 0 | 1000
01-aug-09 | 112 | 1 | 2000
01-aug-09 | 113 | 2 | 900
01-aug-09 | 114 | 3 | 1250
 
 
01-sep-09 | 111 | 1 | 1200
01-sep-09 | 112 | 2 | 2000
01-sep-09 | 113 | 3 | 1900
01-sep-09 | 114 | 4 | 1250
 
01-oct-09 | 111 | 2 | 1100
01-oct-09 | 112 | 3 | 2200
01-oct-09 | 113 | 4 | 1900

Expected result

If customer’s tenure is 0 then mark as ‘New’,
If customer’s balance is increased from last month then mark as ‘Augment’
If customer’s balance is same as last month then mark as ‘Maintain’
If customer’s balance is decreased from last month then mark as ‘Diminish’
Else ‘Left’

Help please....

 

 

The answer is :

with t as (
    select DATE '2009-08-01' Date_value, 111 Cust_id, 0 Customer_tenue, 1000 avg_bal from dual union all
    select DATE '2009-08-01', 112 , 1 , 2000 from dual union all
    select DATE '2009-08-01', 113 , 2 , 900 from dual union all
    select DATE '2009-08-01', 114 , 3 , 1250 from dual union all
    select DATE '2009-09-01', 111 , 1 , 1200 from dual union all
    select DATE '2009-09-01', 112 , 2 , 2000 from dual union all
    select DATE '2009-09-01', 113 , 3 , 1900 from dual union all
    select DATE '2009-09-01', 114 , 4 , 1250 from dual union all
    select DATE '2009-10-01', 111 , 2 , 1100 from dual union all
    select DATE '2009-10-01', 112 , 3 , 2200 from dual union all
    select DATE '2009-10-01', 113 , 4 , 1900 from dual)
   select date_value, cust_id, avg_bal, oldbal,
          case when Customer_tenue=0 then 'NEW'
               when oldbal               when oldbal=avg_bal then 'Maintain'
               when oldbal>avg_bal then 'Diminish'
               else 'Left' end status
     from (select date_value, cust_id, customer_tenue, avg_bal, LEAD(avg_bal) over (partition by cust_id order by date_value desc) oldbal
             from t)
   order by cust_id, date_value;