CTE_1 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,

TO_TIMESTAMP(value[0]::string) AS day_price,

value[1] AS price

FROM

raw_data,

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

,

min_block_timestamp AS (

SELECT MIN(block_timestamp) AS min_timestamp

FROM aptos.core.fact_transactions

SELECT

CTE_1.day_price,

CTE_1.price,

COUNT(DISTINCT FACT_TRANSACTIONS_ID) AS daily_transactions

FROM

aptos.core.fact_transactions

CROSS JOIN

min_block_timestamp

JOIN

CTE_1

ON

CTE_1.day_price = date_trunc('day', block_timestamp)

WHERE

block_timestamp <= DATEADD(day, 180, min_timestamp)

GROUP BY

CTE_1.day_price, CTE_1.price

ORDER BY