title: Sql query to get src to dest path for connecting flights
body: <p>I want to solve a problem where I wish to find src to dest flight path including connecting flights and if possible sort it for time required. </p>
  
  <p>Can I use something  like listagg to aggregate intermediate flights as a string somehow.</p>
  
  <p>We could cap the number of connecting flights to a number and the time taken.</p>
  
  <p>I have this as a start right now which gives me connecting flights</p>
  
  <pre><code>with  cap as (select 30 time_cap , 5 connect_cap), 
   connecting as 
      (select f1.src src1
            , f1.dest dest1
            , f1.stt st1
            , f1.endt end1
            , f2.src src2
            , f2.dest dest2
            , f2.stt st2
            , f2.endt end2
            , (f2.endt - f1.stt) as time 
       from flight f1 
       inner join flight f2 on f1.dest = f2.src 
       where f1.endt &lt; f2.stt)
  </code></pre>
  
  <p>My table looks like this now</p>
  
  <pre><code>\d+ flight
                                    Table "public.flight"
   Column |            Type             | Modifiers | Storage  | Stats target | Description 
  --------+-----------------------------+-----------+----------+--------------+-------------
   src    | character varying(20)       |           | extended |              | 
   dest   | character varying(20)       |           | extended |              | 
   stt    | timestamp without time zone |           | plain    |              | 
   endt   | timestamp without time zone |           | plain    |              | 
  </code></pre>
  
  <p>This was an interview question which already got over.</p>
  
  <p>Could a graph bfs kind of solution be solved in an sql query?</p>
  
  <p>Even a non working query (pseudo code - which would work if tried) or approach would do. </p>
  
  <p>In the below query,
  I want to figure out a way in string_agg where I can check if the last destination is the destination I want to go to. </p>
  
  <pre><code>with flight as (select f1.src||'-'||f1.dest||','||f2.src||'-'||f2.dest route
                       , f1.src src1
                       , f1.dest dest1
                       , f1.stt st1
                       , f1.endt end1
                       , f2.src src2
                       , f2.dest dest2
                       , f2.stt st2
                       , f2.endt end2
                       , (f2.endt - f1.stt) as time 
                  from flight f1 
                  inner join flight f2 on f1.dest = f2.src 
                  where f1.endt &lt; f2.stt) 
  
  select string_agg(route,',') from flight ;
  </code></pre>
  
  <p>The output from query flight      </p>
  
  <pre><code>  route  | src1 | dest1 |         st1         |        end1         | src2 | dest2 |         st2         |        end2         |   time   
  ---------+------+-------+---------------------+---------------------+------+-------+---------------------+---------------------+----------
   a-b,b-c | a    | b     | 2017-05-17 09:31:56 | 2017-05-17 14:31:56 | b    | c     | 2017-05-17 15:31:56 | 2017-05-17 16:31:56 | 07:00:00
  </code></pre>