with res AS (

SELECT live.udf_api(

'https://api.dune.com/api/v1/query/4771344/results?api_key={dune_key}',

--'https://api.dune.com/api/v1/query/2946766/results?api_key={dune}',

'DuneAPI'

):"data":"result":"rows" AS json_result_must_pivot

from DUAL

),

historical_contracts as

(

SELECT

TO_DATE(REPLACE(d.value:"block_time", 'UTC', '')) AS date,

TRY_CAST(NULLIF(d.value:"tx_hash"::STRING, '') AS VARCHAR) AS CREATED_TX_HASH,

TRY_CAST(NULLIF(d.value:"address"::STRING, '') AS VARCHAR) AS Address,

TRY_CAST(NULLIF(d.value:"from"::STRING, '') AS VARCHAR) AS creator_address,

-- TRY_CAST(NULLIF(d.value:"contract_abi"::STRING, '') AS VARCHAR) AS contract_abi,

TRY_CAST(NULLIF(d.value:"name"::STRING, '') AS VARCHAR) AS Name,

TRY_CAST(NULLIF(d.value:"namespace"::STRING, '') AS VARCHAR) AS Symbol

res,

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

),

combined as

( select

created_block_timestamp

, created_tx_hash

, address

, creator_address

, name

, symbol

from ronin.core.dim_contracts