How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY
Test

WITH cte AS ( SELECT origin,
                     provider,
                     price,
                     departure_date,
                     ROW_NUMBER() OVER (PARTITION BY /* origin, */ provider
                                        ORDER BY departure_date) rn 
              FROM flights
              WHERE origin = 'LHR' 
                AND destination IN ('FCO', 'CDG', 'AMS')
                AND cabin_type = 'ECONOMY' )
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte
WHERE rn &lt;= 10
GROUP BY origin, 
         provider;


UPDATE: (draft code, see comments)

WITH 
cte1 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn 
          FROM flights
          WHERE origin = 'LHR' 
            AND destination ='FCO'
            AND cabin_type = 'ECONOMY' ),
cte2 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte1
          WHERE rn &lt;= 10),
cte3 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn 
          FROM flights
          WHERE origin = 'LHR' 
            AND destination = 'CDG'
            AND cabin_type = 'ECONOMY' ),
cte4 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte3
          WHERE rn &lt;= 10),
cte5 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn 
          FROM flights
          WHERE origin = 'LHR' 
            AND destination = 'AMS'
            AND cabin_type = 'ECONOMY' ),
cte6 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte5
          WHERE rn &lt;= 10),
cte7 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte2
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte4
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte6 )
cte8 as ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                        ORDER BY departure_date) rn 
           FROM cte7)
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte8
WHERE rn &lt;= 10
GROUP BY origin, 
         provider;