1WITH
2  ranked_data AS (
3    SELECT
4      periodo,
5      valor,
6      ROW_NUMBER() OVER (
7        PARTITION BY
8          periodo
9        ORDER BY
10          valor 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 valor IS NOT NULL
19  ),
20  parsed_data AS (
21    SELECT
22      periodo,
23      valor AS volume_transacoes,
24      CAST(regexp_extract(periodo, '(\d+)\.º', 1) AS INTEGER) AS trimestre,
25      CAST(
26        regexp_extract(periodo, 'de (\d{4})', 1) AS INTEGER
27      ) AS ano,
28      make_date(
29        CAST(
30          regexp_extract(periodo, 'de (\d{4})', 1) AS INTEGER
31        ),
32        (
33          CAST(regexp_extract(periodo, '(\d+)\.º', 1) AS INTEGER) - 1
34        ) * 3 + 1,
35        1
36      ) AS data_ordenacao
37    FROM
38      ranked_data
39    WHERE
40      rn = 1
41  )
42SELECT
43  periodo,
44  volume_transacoes
45FROM
46  parsed_data
47ORDER BY
48  data_ordenacao ASC