Untitled

WITH source AS (
    SELECT * FROM altavista.globalx_issuer_breakdown
),

ticker_fix AS (
    SELECT
        as_of_date,
        REPLACE(
            REPLACE(etf_ticker, '-', ' '),
            'GB', 'LN'
        ) AS etf_ticker,
        idx,
        issuer_type,
        weight
    FROM source
),

filtered AS (
    SELECT *
    FROM ticker_fix
    WHERE as_of_date >= '2023-01-01' 
      AND as_of_date IS NOT NULL
),

jsonified AS (
    SELECT
        as_of_date,
        etf_ticker,
        OBJECT_CONSTRUCT(
            'as_of_date', as_of_date,
            'issuers', OBJECT_AGG(issuer_type, CAST(weight AS NUMERIC(5, 4)))
        ) AS issuer_breakdown
    FROM filtered
    GROUP BY as_of_date, etf_ticker
)

SELECT * FROM jsonified;