4 Replies Latest reply on Oct 6, 2018 9:28 AM by Avi Meir

    Finding the cheapest supplier, and average distance from it

    Avi Meir

      Hi all, I have the following data:

             

      idamountcurrencyproviderflight_designatorsclasstransaction_id
      1996.45EURprov1{LX242,LX243}economy123
      21010.49EURprov3{LX242,LX243}economy123
      31005.23EURprov4{LX242,LX243}economy123
      42938EURprov1{LX242,LX243}economy-comfort123
      52931EURprov2{LX242,LX243}economy-comfort123
      63002EURprov4{LX242,LX243}economy-comfort123
      7720EURprov1{LH2305,AF1820}economy123
      8910EURprov3{LH2305,AF1820}economy123
      9801EURprov4{LH2305,AF1820}economy123
      101028EURprov1{LH2305,AF1820}economy-comfort123
      111038EURprov2{LH2305,AF1820}economy-comfort123
      121000EURprov4{LH2305,AF1820}economy-comfort123

          

      I am trying to do the following:

       

      For each transaction ID, class, and flight_designators combination:

      1. show the cheapest provider
      2. for each provider, show the distance, in %, from the cheapest provider

       

      So in the example above, it should look as follows:

       

             

      idamountcurrencyproviderflight_designatorsclasstransaction_idcheapestdistance from cheapest
      1996.45EURprov1{LX242,LX243}economy123prov10%
      21010.49EURprov3{LX242,LX243}economy123prov11.41%
      31005.23EURprov4{LX242,LX243}economy123prov10.88%
      42938EURprov1{LX242,LX243}economy-comfort123prov20.24%
      52931EURprov2{LX242,LX243}economy-comfort123prov20.00%
      63002EURprov4{LX242,LX243}economy-comfort123prov22.42%
      7720EURprov1{LH2305,AF1820}economy123prov10%
      8910EURprov3{LH2305,AF1820}economy123prov126.39%
      9801EURprov4{LH2305,AF1820}economy123prov111.25%
      101028EURprov1{LH2305,AF1820}economy-comfort123prov42.80%
      111038EURprov2{LH2305,AF1820}economy-comfort123prov43.80%
      121000EURprov4{LH2305,AF1820}economy-comfort123prov40.00%