How to return only those customer_id who visited Santa Clara after visiting Milpitas or #5 after visiting #2

Tags: , ,



Customer_ID  Region    Location      Store_Entry
--------------------------------------------------------
   D1          2       Milpitas      '2020-10-01' 14:03
   D2          5       Santa Clara   '2020-10-01' 15:16
   D2          5       Santa Clara   '2020-10-01' 16:00
   D3          4       Milpitas      '2020-10-01' 16:12
   D1          3       Los Gatos     '2020-10-01' 16:23
   D2          2       Milpitas      '2020-10-01' 17:01

How can I return the customer_id of those customers who visited Santa Clara after visiting Milpitas or #5 after visiting #2?

Answer

Use conditional aggregation with a having clause:

select customer_id
from t
where location in ('Santa Clara', 'Milpitas')
group by customer_id
having max(store_entry) filter (where location = 'Santa Clara') >
       min(store_entry) filter (where location = 'Milpitas');

Your statement is that the customer when to after Santa Clara after Milpitas. This leaves open the possibility that the customer visited both locations multiple times.

To meet the condition as stated, you need to know that some visit to Santa Clara was later than some visit to Milpitas (at least that is how I interpret the question). Well, that is true if the lastest visit to Santa Clara was after the earliest visit to Milpitas.



Source: stackoverflow