SQL – How to find if the combination of column has occured before or not?

Following example demonstrates the question

id	location	dt
1	India	2020-01-01
2	Usa	2020-02-01
1	Usa	2020-03-01
3	China	2020-04-01
1	India	2020-05-01
2	France	2020-06-01
1	India	2020-07-01
2	Usa	2020-08-01

This table is sorted by date. I want to create another column, which would tell if the id has been to the location before or not.

So, The output would be like

id	location	dt	travelled
1	India	2020-01-01	0
2	Usa	2020-02-01	0
1	Usa	2020-03-01	0
3	China	2020-04-01	0
1	India	2020-05-01	1
2	France.	2020-06-01	0
1	India	2020-07-01	1
2	Usa	2020-08-01	1

The issue I am facing is, For every row, I need to consider only the rows above it.

Answer

Use EXISTS in a CASE expression:

SELECT t1.id, t1.location,
       CASE 
         WHEN EXISTS (
           SELECT 1 
           FROM tablename t2 
           WHERE t2.id = t1.id AND t2.location = t1.location AND t2.date < t1.date 
         ) THEN 1 
         ELSE 0 
       END travelled
FROM tablename t1