zakkisyedBJP - Electoral Bonds Data copy
    Updated 2024-03-14
    -- forked from BJP - Electoral Bonds Data @ https://flipsidecrypto.xyz/edit/queries/b2cc40c7-e337-4dea-bcf9-5dc274915c57

    WITH res AS (
    SELECT
    livequery.live.udf_api(
    'GET',
    'https://science.flipsidecrypto.xyz/googlesheets/readsheet',
    {'Content-Type': 'application/json'},
    {
    'sheets_id': '1YfgsP2UGAkeq3GOFVNvjOkI0o3sSog37LpXlX7hZWa0',
    'tab_name': 'Sheet1'
    }
    ) AS result
    FROM DUAL
    ),
    data AS (
    SELECT result:data AS json_result_must_pivot FROM res
    ),
    final_eci_data AS (
    SELECT
    TO_DATE(d.value:"Date"::VARCHAR, 'DD-MM-YYYY') AS Date,
    d.value:"Political Party"::VARCHAR AS "Political Party",
    TO_NUMBER(d.value:"Amount Encashed") AS "Amount Encashed"
    FROM
    data,
    LATERAL FLATTEN(input => data.json_result_must_pivot::VARIANT) d
    ),
    aggregated_data AS (
    SELECT
    Date,
    "Political Party",
    SUM("Amount Encashed") AS "Total Amount Encashed"
    FROM final_eci_data
    GROUP BY Date, "Political Party"
    ),
    cumulative_data AS (
    QueryRunArchived: QueryRun has been archived