Comments: Advent of Code 2022 in Oracle - Day 1

[phartenfeller]

Blogpost: https://hartenfeller.dev/blog/advent-of-code-in-oracle-day-1

Comments on this issue are displayed on the blogpost.

[edhenrique]

I solved the challenge using only one SELECT expression

WITH input AS
 (SELECT '1000
2000
3000

4000

5000
6000

7000
8000
9000

10000
' l_input
    FROM dual)
,split_input AS (    
  SELECT COLUMN_VALUE calories
       , rownum linha
       , CASE WHEN column_value IS NULL THEN rownum END break_elfo
    FROM input
        ,apex_string.split(l_input, chr(10)) x)
,get_break_elfo AS (      
  SELECT '0,'||listagg(break_elfo, ',') RANGE
   FROM split_input b)
,get_range_elfo AS ( 
  SELECT  COLUMN_VALUE+1 start_row
        , LEAD(COLUMN_VALUE -1,1,999) OVER(ORDER BY to_number(COLUMN_VALUE)) end_row
    FROM get_break_elfo  
        ,apex_string.split(RANGE,','))
SELECT RANK() OVER(ORDER BY start_row) ID
     , SUM(v.calories) calories
     , RANK() OVER (ORDER BY SUM(v.calories) DESC) rank_calories
     , CASE WHEN RANK() OVER (ORDER BY SUM(v.calories) DESC) <=3 THEN 'Yes' END top_rank_calories     
  FROM get_range_elfo r
      ,split_input v      
WHERE v.linha BETWEEN r.start_row AND r.end_row
GROUP BY start_row, end_row 
ORDER BY start_row