0

I have a table with below structure and sample data:

create table TEST_ONE
(
  purchase_date DATE,
  customer_id   NUMBER
)

   | purchase_date  | customer_id   
----------------------------------
   |   1/1/2000     |    1
   |   1/1/2000     |    2
   |   1/1/2000     |    3
   |   1/2/2000     |    1
   |   1/2/2000     |    2
   |   1/2/2000     |    6
   |   1/3/2000     |    1
   |   1/3/2000     |    2
   |   1/3/2000     |    7

For each customer_id , I want to show whether it has purchased something in the day before or not. The output must be like below (I do not want to show anything for the start_date (I mean the first (min) date) which is 1/1/2000 here ,

   | purchase_date  | customer_id |   Flag
------------------------------------------
   |   1/1/2000     |    1        |  
   |   1/1/2000     |    2        |
   |   1/1/2000     |    3        |
   |   1/2/2000     |    1        |   TRUE
   |   1/2/2000     |    2        |   TRUE
   |   1/2/2000     |    6        |   FALSE
   |   1/3/2000     |    1        |   TRUE
   |   1/3/2000     |    2        |   TRUE
   |   1/3/2000     |    7        |   FALSE

What I've written so far is this :

select B.*  ,
     case
       when 
       nvl((SELECT 'TRUE'
        FROM TEST_ONE A
        WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
        AND A.PURCHASE_DATE = B.PURCHASE_DATE -1
        ),'false' )   
from TEST_ONE b  

The problem is that :

  1. I think better query must be possible to solve this,
  2. The nvl function applies to all records but I do not need to show any flag for the minimum date
  3. I tried to sole it with LAG() function but I did not succeed

Thanks in advance

1

It sounds like you want to join the table to itself on the customer_id and purchase_date offest by one day. Give this a try.

select    a.purchase_date
        , a.customer_id
        , case when b.customer_id is null then 'FALSE'
               else 'TRUE'
          end flag
from      test_one a
left join test_one b on b.customer_id=a.customer_id and b.purchase_date=a.purchase_date-1
where     a.purchase_date<>(select min(purchase_date) from test_one)
order by  a.purchase_date
        , a.customer_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.