SELECT

cohort_month,

COUNT(ORIGIN_FROM_ADDRESS) AS new_traders

FROM

(SELECT

MIN(DATE_TRUNC('day',block_timestamp)) AS cohort_month,

ORIGIN_FROM_ADDRESS

FROM

ethereum.defi.ez_dex_swaps

WHERE

platform = 'uniswap-v3'

GROUP BY

ORIGIN_FROM_ADDRESS

)

GROUP BY

cohort_month

),

gas_price_per_tx AS (

SELECT

DATE_TRUNC('day', block_timestamp) as month,

AVG(gas_price) avg_gas_price

FROM

(

SELECT

swap.block_timestamp,

swap.tx_hash,

origin_from_address,

tx.gas_price,

ROW_NUMBER() OVER(PARTITION BY swap.tx_hash order by swap.block_timestamp) as row_id

FROM

ethereum.defi.ez_dex_swaps swap

LEFT JOIN ethereum.core.fact_transactions tx

ON (swap.tx_hash = tx.tx_hash AND swap.block_timestamp = tx.block_timestamp)