WITH

prices AS (

WITH raw_data AS (

SELECT livequery.live.udf_api('https://api.coingecko.com/api/v3/coins/aptos/market_chart?vs_currency=usd&days=max') AS resp

)

SELECT

'prices' AS key,

date_trunc('day', TO_TIMESTAMP(value[0]::string)) AS day, -- Truncate the timestamp to align with bridge dates

value[1] AS price

FROM

raw_data,

LATERAL FLATTEN (input => resp:data:prices)

),

bridge AS (

SELECT

date_trunc('day', block_timestamp) as date,

COUNT(DISTINCT TX_HASH) as total_txs

FROM

ethereum.defi.ez_bridge_activity

WHERE

block_timestamp > '{{starting_date}}'

AND Destination_chain = 'aptos'

GROUP BY

date

SELECT b.date AS day, COALESCE(total_txs, 0) AS total_txs, price

FROM bridge b

LEFT JOIN prices p ON b.date = p.day

WHERE (b.date >= '{{starting_date}}' OR b.date >= (SELECT MAX(date) FROM bridge));