WITH res AS (

SELECT

livequery.live.udf_api(

'GET',

'https://science.flipsidecrypto.xyz/googlesheets/readsheet',

{'Content-Type': 'application/json'},

{

'sheets_id': '1YfgsP2UGAkeq3GOFVNvjOkI0o3sSog37LpXlX7hZWa0',

'tab_name': 'Sheet2'

}

) 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:"Name of Entity"::VARCHAR AS "Name of Entity",

TO_NUMBER(d.value:"Donation") AS "Donation"

FROM

data,

LATERAL FLATTEN(input => data.json_result_must_pivot::VARIANT) d

),

aggregated_data AS (

SELECT

Date,

"Name of Entity",

SUM("Donation") AS "Total Amount Donated"

FROM final_eci_data

GROUP BY Date, "Name of Entity"

),