The below data is at the trip level, a unique trip per row. I'm looking to set a 0 / 1 boolean when the same person has RETURNED to the same location, meaning their first trip to a destination will not have the 1 indicator. Ideally, I can do this with a window function but am open to other thoughts if we cannot compare a value within a partition against all of the other values within partition.
My initial attempt only compared against one record within the partition and won't pick up if they went back to the same Destination on a trip beyond their next one.
My initial attempt was: CASE WHEN Destination = LAG(Destination) OVER (PARTITION BY Person) THEN 1 ELSE 0 END
STARTING DATA EXAMPLE BELOW
| Person | Trip | Destination |
|---|---|---|
| Bob | a | Hawaii |
| Bob | b | Hawaii |
| Bob | c | Florida |
| Kevin | a | Texas |
| Kevin | b | Iowa |
| Tina | a | Arizona |
| Tina | b | Arizona |
| Tina | c | California |
| Tina | d | New Mexico |
| Tina | e | Arizona |
| Tina | e | California |
DESIRED OUTPUT BELOW
| Person | Trip | Destination | Same_Destination |
|---|---|---|---|
| Bob | a | Hawaii | 0 |
| Bob | b | Hawaii | 1 |
| Bob | c | Florida | 0 |
| Kevin | a | Texas | 0 |
| Kevin | b | Iowa | 0 |
| Tina | a | Arizona | 0 |
| Tina | b | Arizona | 1 |
| Tina | c | California | 0 |
| Tina | d | New Mexico | 0 |
| Tina | e | Arizona | 1 |
| Tina | f | California | 1 |
You can modify your condition to use first_value:
case when
destination = First_Value(destination) over(partition by person order by trip rows unbounded preceeding)
and trip != First_Value(trip) over(partition by person order by trip rows unbounded preceeding)
then 1 else 0 end
Edit
Based on your addition comments I think this would be simpler to implement using a correlated subquery. Does the following work for you?
select *, case when exists (
select * from t t2
where t2.person = t.person
and t2.destination = t.destination
and t2.trip < t.trip
) then 1 else 0 end
from t;