Thank you for your reply. here, i am explaining the problem.
This is the Problem: Count, for each couple of stations, the number of unique trips on which the two stations in the corresponding couple are visited right after each other. Two stations are visited right after each other on the same trip if the difference between the corresponding stop numbers equals 1.
In the result table, we expect three columns with corresponding datatype: station1 (varchar), station2 (varchar) and trips_with_consecutive_visits (integer). Make sure that each couple only appears once in the result table by enforcing that, for each row, the value (i.e. station name) in station1 is sorted alphabetically before the value (i.e. station name) in station2. As such, the count associated with the row of station1 and station2 should include the number of unique trips that (1) have a stop in station2 directly after station1 and (2) have a stop in station1 directly after station2. Do not list couples of stations that are never visited right after each other (i.e. couples with count 0)
I have a dat set with, code, stop_number, station_name