CODEWITHSUNDEEP
sqllogicteradata
sai
sai

Reputation: 323

SQL nested logic

This is my table structure

CUST_ID  ORDER_DT
1        01-2013
1        04-2013
1        01-2015
1        02-2015

What I am trying to achieve is classify the customer as new customer/existing customer and revived. Logic is First time order- New Time from last purchase within 365 days then Existing Time more than 1 year then Revived

My output should be 

CUST_ID  ORDER_DT  FLAG
1         01-2013  New
1         04-2013  Exisiting
1         01-2015  Revived
1         02-2015  Exisiting

My SQL 

select a.cust_id,a.order_dt,coalesce(b.ptye,'other') as typ
from tab a left join
  (select min(order_dt),new as ptye from tab group by cust_id) b on a.cust_id=b.cust_id

How do I replace the other with a nested logic.

Upvotes: 0

Views: 70

Answers (3)

dnoeth
dnoeth

Reputation: 60482

This includes the "revived" logic which was missing in Gordon's answer:

SELECT 
   CUST_ID, ORDER_DT,
   CASE
      WHEN ORDER_DT = MIN(ORDER_DT)  -- first order 
                      OVER (PARTITION BY CUST_ID)
         THEN 'New'
      WHEN ORDER_DT >= MAX(ORDER_DT)  -- more than 365 days since previous order
                       OVER (PARTITION BY CUST_ID
                             ORDER BY ORDER_DT
                             ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + 365
         THEN 'Revived'
      ELSE 'Existing'
   END
FROM tab

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270733

The idea way would be to use lag(). Teradata doesn't quite support lag, but it does support other window functions. So, you can mimic it:

select t.cust_id, t.order_dt,
       (case when order_dt - prev_od <= 365 then 'Existing' else 'New'
        end) as flag
from (select t.*,
             max(order_dt) over (partition by cust_id order by order_dt
                                 rows between 1 preceding and 1 preceding
                                ) as prevod
      from mytable t
     ) t;

I should point out that you don't actually need the subquery, but I think it helps readability:

select t.cust_id, t.order_dt,
       (case when order_dt -
                  max(order_dt) over (partition by cust_id order by order_dt
                                 rows between 1 preceding and 1 preceding
                                ) <= 365
             then 'Existing' else 'New'
        end) as flag
from (select t.*,
              as prevod
      from mytable t
     ) t;

Upvotes: 1

thomas
thomas

Reputation: 91

A very simple way to do this would be to use a case statement with subqueries:

select cust_id
, order_dt
, flag = case
             when (select COUNT(*) from myTable x where t.cust_id= x.cust_id and x.order_dt < t.order_dt and DATEDIFF(DD, x.order_dt , t.order_dt ) < 365) then 'Existing'
             when (select COUNT(*) from myTable x where t.cust_id= x.cust_id and x.order_dt < t.order_dt and DATEDIFF(DD, x.order_dt , t.order_dt ) >= 365) then 'Revived'
             else 'New'
         end
from myTable t

Upvotes: 0

Related Questions