Untitled
unknown
plain_text
6 months ago
739 B
4
Indexable
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;
Editor is loading...
Leave a Comment