1WITH
2  ranked_data AS (
3    SELECT
4      periodo,
5      CAST(replace(ind_string, ' ', '') AS BIGINT) AS num_transacoes,
6      ROW_NUMBER() OVER (
7        PARTITION BY
8          periodo
9        ORDER BY
10          CAST(replace(ind_string, ' ', '') AS BIGINT) DESC
11      ) AS rn
12    FROM
13      "@ine.indicador_0012786.raw"
14    WHERE
15      geodsg = 'Portugal'
16      AND domic_lio_fiscal_do_comprador_categ_dsg = 'Total'
17      AND setor_institucional_do_comprador_categ_dsg = 'Total'
18      AND ind_string IS NOT NULL
19  ),
20  parsed_data AS (
21    SELECT
22      periodo,
23      num_transacoes,
24      make_date(
25        CAST(
26          regexp_extract(periodo, 'de (\d{4})', 1) AS INTEGER
27        ),
28        (
29          CAST(regexp_extract(periodo, '(\d+)\.º', 1) AS INTEGER) - 1
30        ) * 3 + 1,
31        1
32      ) AS data_ordenacao
33    FROM
34      ranked_data
35    WHERE
36      rn = 1
37  )
38SELECT
39  periodo,
40  num_transacoes
41FROM
42  parsed_data
43ORDER BY
44  data_ordenacao ASC