WITH prices AS (

SELECT

date_trunc('hour', RECORDED_HOUR) as day,

avg(price) as price

FROM

osmosis.price.ez_prices

WHERE

symbol = 'INJ'

AND RECORDED_HOUR > '{{starting_date}}' -- Using RECORDED_HOUR instead of HOUR for filtering

GROUP BY

day

),

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 = 'injective'

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));