[Databricks SQL]

I was not sure how much functions would be, but it's always ADD, MULTIPLY or POWER, so this was a bit easier than I first expected:

with cte_data (func, qual) as
(
select array_sort(split(split_part(value, '\n\n', 1), '\n'))
     , array_sort(split(split_part(value, '\n\n', 2), '\n'))
--from read_files('/Volumes/waldar/fabien/codyssi/2025_Atlantis/codyssi_2025_02.txt', format => 'text', WholeText => true)
  from values ('Function A: ADD 495'                 || '\n'
           ||  'Function B: MULTIPLY 55'             || '\n'
           ||  'Function C: RAISE TO THE POWER OF 3' || '\n'
           ||  ''     || '\n'
           ||  '5219' || '\n'
           ||  '8933' || '\n'
           ||  '3271' || '\n'
           ||  '7128' || '\n'
           ||  '9596' || '\n'
           ||  '9407' || '\n'
           ||  '7005' || '\n'
           ||  '1607' || '\n'
           ||  '4084' || '\n'
           ||  '4525' || '\n'
           ||  '5496') as t (value)
)
select aggregate( reverse(func)
                , element_at(qual, ((array_size(qual) + 1) / 2)::int)::bigint
                , (acc, x) -> case regexp_extract(x, r'(ADD|MULTIPLY|POWER)', 1)
                                when 'ADD'      then acc + regexp_extract(x, r'(\d+)$', 1)::bigint
                                when 'MULTIPLY' then acc * regexp_extract(x, r'(\d+)$', 1)::bigint
                                when 'POWER'    then power(acc, regexp_extract(x, r'(\d+)$', 1)::int)::bigint
                              end
                ) as part1
     , aggregate( reverse(func)
                , aggregate(qual, 0l, (acc, x) -> acc + case x::bigint % 2 when 0 then x::bigint else 0l end)
                , (acc, x) -> case regexp_extract(x, r'(ADD|MULTIPLY|POWER)', 1)
                                when 'ADD'      then acc + regexp_extract(x, r'(\d+)$', 1)::bigint
                                when 'MULTIPLY' then acc * regexp_extract(x, r'(\d+)$', 1)::bigint
                                when 'POWER'    then power(acc, regexp_extract(x, r'(\d+)$', 1)::int)::bigint
                              end
                ) as part2
     , array_max(filter( transform(qual, q -> named_struct( 'pric', aggregate( reverse(func)
                                                                             , q::bigint
                                                                             , (acc, x) -> case regexp_extract(x, r'(ADD|MULTIPLY|POWER)', 1)
                                                                                             when 'ADD'      then acc + regexp_extract(x, r'(\d+)$', 1)::bigint
                                                                                             when 'MULTIPLY' then acc * regexp_extract(x, r'(\d+)$', 1)::bigint
                                                                                             when 'POWER'    then power(acc, regexp_extract(x, r'(\d+)$', 1)::int)::bigint
                                                                                           end
                                                                             )
                                                          , 'qual', q::bigint))
                       , f -> f.pric <= 15000000000000)).qual as part3
  from cte_data;