with price as ( select DATE_TRUNC( {{frequency}}, RECORDED_HOUR ) AS DATE, avg(case when id = 'ethereum' then close end) as eth_price, avg(case when id = 'avalanche-2' then close end) as avax_price, avg(case when id = 'binancecoin' then close end) as bnb_price, avg(case when id = 'xdai' then close end) as xdai_price, avg(case when id = 'near' then close end) as near_price, avg(case when id = 'optimism' then close end) as op_price, avg(case when id = 'osmosis' then close end) as osmos_price, avg(case when id = 'matic-network' then close end) as matic_price, avg(case when id = 'solana' then close end) as sol_price FROM crosschain.core.fact_hourly_prices group by date ), eth as ( select t.*, p.eth_price as price, p.eth_price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(FROM_ADDRESS)) as user, COUNT(TX_HASH) as tx, sum(tx_fee) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from ethereum.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), arb as ( select t.*, p.eth_price as price, p.eth_price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(FROM_ADDRESS)) as user, COUNT(TX_HASH) as tx, sum(tx_fee) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from arbitrum.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), avax as ( select t.*, p.avax_price as price, p.avax_price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(FROM_ADDRESS)) as user, COUNT(TX_HASH) as tx, sum(tx_fee) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from avalanche.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), bsc as ( select t.*, p.bnb_price as price, price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(FROM_ADDRESS)) as user, COUNT(TX_HASH) as tx, sum(tx_fee) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from bsc.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), axelar as ( select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(SENDER)) as user, COUNT(TX_ID) as tx, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u from axelar.core.fact_transfers where date >= date_trunc('day', {{time_from}}) and date < date_trunc('day', {{time_to}}) GROUP by date ), flow as ( select t.* from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(PROPOSER)) as user, COUNT(TX_ID) as tx, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from flow.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), gnosis as ( select t.*, p.xdai_price as price, price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(from_address)) as user, COUNT(TX_hash) as tx, sum(TX_FEE) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from gnosis.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), near as ( select t.*, p.near_price as price, price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(TX_SIGNER)) as user, COUNT(TX_hash) as tx, sum(TRANSACTION_FEE/1e24) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from near.core.fact_transfers GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), op as ( select t.*, p.op_price as price, price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(FROM_ADDRESS)) as user, COUNT(TX_HASH) as tx, sum(tx_fee) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from optimism.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), osmos as ( select t.*, p.osmos_price as price, price * t.fee1 as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(TX_FROM)) as user, COUNT(TX_ID) as tx, sum(case when fee LIKE '%uosmo%' then REVERSE(substr(REVERSE(fee),6))/1e6 ELSE 0 END) as fee1, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from osmosis.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), polygon as ( select t.*, p.matic_price as price, price * t.fee as fee_usd, ln((fee_usd - lag(fee_usd) over(order by t.date))/lag(fee_usd) over(order by t.date) + 1)as pct_f from (select DATE_TRUNC( {{frequency}}, block_timestamp ) AS DATE, count(DISTINCT(FROM_ADDRESS)) as user, COUNT(TX_HASH) as tx, sum(tx_fee) as fee, ln((user - lag(user) over(order by date))/lag(user) over(order by date) + 1)as pct_u, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from polygon.core.fact_transactions GROUP by date) t left join price p on t.date = p.date where t.date >= date_trunc('day', {{time_from}}) and t.date < date_trunc('day', {{time_to}}) ), stark as (select DATE_TRUNC( {{frequency}}, TIMESTAMP ) AS DATE, count(TX_HASH) as tx, ln((tx - lag(tx) over(order by date))/lag(tx) over(order by date) + 1)as pct_t from tokenflow_starknet.decoded.transactions where CHAIN_ID = 'mainnet' and date >= date_trunc('day', {{time_from}}) and date < date_trunc('day', {{time_to}}) GROUP BY date), cal as ( select -- eth eth.date as date, sum(eth.pct_u) over(order by eth.date) as eth_user_pct1, sum(eth.pct_t) over(order by eth.date) as eth_tx_pct1, sum(eth.pct_f) over(order by eth.date) as eth_fee_pct1, round(exp(eth_user_pct1),2)-1 as eth_user_pct, round(exp(eth_tx_pct1),2)-1 as eth_tx_pct, round(exp(eth_fee_pct1),2)-1 as eth_fee_pct, -- arb sum(arb.pct_u) over(order by eth.date) as arb_user_pct1, sum(arb.pct_t) over(order by eth.date) as arb_tx_pct1, sum(arb.pct_f) over(order by eth.date) as arb_fee_pct1, round(exp(arb_user_pct1),2)-1 as arb_user_pct, round(exp(arb_tx_pct1),2)-1 as arb_tx_pct, round(exp(arb_fee_pct1),2)-1 as arb_fee_pct, -- avax sum(avax.pct_u) over(order by eth.date) as avax_user_pct1, sum(avax.pct_t) over(order by eth.date) as avax_tx_pct1, sum(avax.pct_f) over(order by eth.date) as avax_fee_pct1, round(exp(avax_user_pct1),2) -1 as avax_user_pct, round(exp(avax_tx_pct1),2) -1 as avax_tx_pct, round(exp(avax_fee_pct1),2)-1 as avax_fee_pct, -- bsc sum(bsc.pct_u) over(order by eth.date) as bsc_user_pct1, sum(bsc.pct_t) over(order by eth.date) as bsc_tx_pct1, sum(bsc.pct_f) over(order by eth.date) as bsc_fee_pct1, round(exp(bsc_user_pct1),2)-1 as bsc_user_pct, round(exp(bsc_tx_pct1),2)-1 as bsc_tx_pct, round(exp(bsc_fee_pct1),2)-1 as bsc_fee_pct, -- axelar sum(axelar.pct_u) over(order by eth.date) as axelar_user_pct1, sum(axelar.pct_t) over(order by eth.date) as axelar_tx_pct1, round(exp(axelar_user_pct1),2)-1 as axelar_user_pct, round(exp(axelar_tx_pct1),2)-1 as axelar_tx_pct, -- flow sum(flow.pct_u) over(order by eth.date) as flow_user_pct1, sum(flow.pct_t) over(order by eth.date) as flow_tx_pct1, round(exp(flow_user_pct1),2)-1 as flow_user_pct, round(exp(flow_tx_pct1),2)-1 as flow_tx_pct, -- gnosis sum(gnosis.pct_u) over(order by eth.date) as gnosis_user_pct1, sum(gnosis.pct_t) over(order by eth.date) as gnosis_tx_pct1, sum(gnosis.pct_f) over(order by eth.date) as gnosis_fee_pct1, round(exp(gnosis_user_pct1),2)-1 as gnosis_user_pct, round(exp(gnosis_tx_pct1),2)-1 as gnosis_tx_pct, round(exp(gnosis_fee_pct1),2)-1 as gnosis_fee_pct, -- near sum(near.pct_u) over(order by eth.date) as near_user_pct1, sum(near.pct_t) over(order by eth.date) as near_tx_pct1, sum(near.pct_f) over(order by eth.date) as near_fee_pct1, round(exp(near_user_pct1),2)-1 as near_user_pct, round(exp(near_tx_pct1),2)-1 as near_tx_pct, round(exp(near_fee_pct1),2)-1 as near_fee_pct, -- op sum(op.pct_u) over(order by eth.date) as op_user_pct1, sum(op.pct_t) over(order by eth.date) as op_tx_pct1, sum(op.pct_f) over(order by eth.date) as op_fee_pct1, round(exp(op_user_pct1),2)-1 as op_user_pct, round(exp(op_tx_pct1),2)-1 as op_tx_pct, round(exp(op_fee_pct1),2)-1 as op_fee_pct, -- osmos sum(osmos.pct_u) over(order by eth.date) as osmos_user_pct1, sum(osmos.pct_t) over(order by eth.date) as osmos_tx_pct1, sum(osmos.pct_f) over(order by eth.date) as osmos_fee_pct1, round(exp(osmos_user_pct1),2)-1 as osmos_user_pct, round(exp(osmos_tx_pct1),2)-1 as osmos_tx_pct, round(exp(osmos_fee_pct1),2)-1 as osmos_fee_pct, -- polygon sum(polygon.pct_u) over(order by eth.date) as polygon_user_pct1, sum(polygon.pct_t) over(order by eth.date) as polygon_tx_pct1, sum(polygon.pct_f) over(order by eth.date) as polygon_fee_pct1, round(exp(polygon_user_pct1),2)-1 as polygon_user_pct, round(exp(polygon_tx_pct1),2)-1 as polygon_tx_pct, round(exp(polygon_fee_pct1),2)-1 as polygon_fee_pct, -- stark sum(stark.pct_t) over(order by eth.date) as stark_tx_pct1, round(exp(stark_tx_pct1),2)-1 as stark_tx_pct -- sol -- sum(sol.pct_u) over(order by eth.date) as sol_user_pct1, -- sum(sol.pct_t) over(order by eth.date) as sol_tx_pct1, -- sum(sol.pct_f) over(order by eth.date) as sol_fee_pct1, -- round(exp(sol_user_pct1),2)-1 as sol_user_pct, -- round(exp(sol_tx_pct1),2)-1 as sol_tx_pct, -- round(exp(sol_fee_pct1),2)-1 as sol_fee_pct FROM eth left join arb on eth.date = arb.date left join avax on eth.date = avax.date left join bsc on eth.date = bsc.date left join axelar on eth.date = axelar.date left join flow on eth.date = flow.date left join gnosis on eth.date = gnosis.date left join near on eth.date = near.date left join op on eth.date = op.date left join osmos on eth.date = osmos.date left join polygon on eth.date = polygon.date left join stark on eth.date = stark.date -- left join sol on eth.date = sol.date ) select -- eth eth.date as date, avg(cal.eth_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as eth_user_ma, avg(cal.eth_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as eth_tx_ma, avg(cal.eth_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as eth_fee_ma, eth.tx as eth_tx, eth.user as eth_user, eth.fee as eth_fee, eth.price as eth_price, eth.fee_usd as eth_fee_usd, avg(eth.tx/eth.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as eth_tx_per_user_ma, avg(eth.fee_usd/eth.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as eth_fee_per_user_ma, avg(eth.fee_usd/eth.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as eth_fee_per_tx_ma, -- arb avg(cal.arb_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as arb_user_ma, avg(cal.arb_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as arb_tx_ma, avg(cal.arb_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as arb_fee_ma, arb.tx as arb_tx, arb.user as arb_user, arb.fee as arb_fee, arb.price as eth_price, arb.fee_usd as arb_fee_usd, avg(arb.tx/arb.user ) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as arb_tx_per_user_ma, avg(arb.fee_usd/arb.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as arb_fee_per_user_ma, avg(arb.fee_usd/arb.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as arb_fee_per_tx_ma, -- avax avg(cal.avax_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as avax_user_ma, avg(cal.avax_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as avax_tx_ma, avg(cal.avax_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as avax_fee_ma, avax.tx as avax_tx, avax.user as avax_user, avax.fee as av_fee, avax.price as avax_price, avax.fee_usd as avax_fee_usd, avg(avax.tx/avax.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as avax_tx_per_user_ma, avg(avax.fee_usd/avax.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as avax_fee_per_user_ma, avg(avax.fee_usd/avax.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as avax_fee_per_tx_ma, -- bsc avg(cal.bsc_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as bsc_user_ma, avg(cal.bsc_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as bsc_tx_ma, avg(cal.bsc_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as bsc_fee_ma, bsc.tx as bsc_tx, bsc.user as bsc_user, bsc.fee as bsc_fee, bsc.price as bsc_price, bsc.fee_usd as bsc_fee_usd, avg(bsc.tx/bsc.user ) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as bsc_tx_per_user_ma, avg(bsc.fee_usd/bsc.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as bsc_fee_per_user_ma, avg(bsc.fee_usd/bsc.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as bsc_fee_per_tx_ma, -- axelar avg(cal.axelar_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as axelar_user_ma, avg(cal.axelar_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as axelar_tx_ma, axelar.tx as axelar_tx, axelar.user as axelar_user, avg(axelar.tx/axelar.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as flow_tx_per_user_ma, -- flow avg(cal.flow_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as flow_user_ma, avg(cal.flow_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as flow_tx_ma, flow.tx as flow_tx, flow.user as flow_user, avg(flow.tx/flow.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as flow_tx_per_user_ma, -- gnosis avg(cal.gnosis_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as gnosis_user_ma, avg(cal.gnosis_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as gnosis_tx_ma, avg(cal.gnosis_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as gnosis_fee_ma, gnosis.tx as gnosis_tx, gnosis.user as gnosis_user, gnosis.fee as gnosis_fee, gnosis.price as gnosis_price, gnosis.fee_usd as gnosis_fee_usd, avg(gnosis.tx/gnosis.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as gnosis_tx_per_user_ma, avg(gnosis.fee_usd/gnosis.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as gnosis_fee_per_user_ma, avg(gnosis.fee_usd/gnosis.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as gnosis_fee_per_tx_ma, -- near avg(cal.near_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as near_user_ma, avg(cal.near_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as near_tx_ma, avg(cal.near_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as near_fee_ma, near.tx as near_tx, near.user as near_user, near.fee as near_fee, near.price as near_price, near.fee_usd as near_fee_usd, avg(near.tx/near.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as near_tx_per_user_ma, avg(near.fee_usd/near.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as near_fee_per_user_ma, avg(near.fee_usd/near.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as near_fee_per_tx_ma, -- op avg(cal.op_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as op_user_ma, avg(cal.op_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as op_tx_ma, avg(cal.op_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as op_fee_ma, op.tx as op_tx, op.user as op_user, op.fee as op_fee, op.price as op_price, op.fee_usd as op_fee_usd, avg(op.tx/op.user ) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as op_tx_per_user_ma, avg(op.fee_usd/op.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as op_fee_per_user_ma, avg(op.fee_usd/op.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as op_fee_per_tx_ma, -- osmos avg(cal.osmos_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as osmos_user_ma, avg(cal.osmos_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as osmos_tx_ma, avg(cal.osmos_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as osmos_fee_ma, osmos.tx as osmos_tx, osmos.user as osmos_user, osmos.fee1 as osmos_fee, osmos.price as osmos_price, osmos.fee_usd as osmos_fee_usd, avg(osmos.tx/osmos.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as osmos_tx_per_user_ma, avg(osmos.fee_usd/osmos.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as osmos_fee_per_user_ma, avg(osmos.fee_usd/osmos.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as osmos_fee_per_tx_ma, -- polygon avg(cal.polygon_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as polygon_user_ma, avg(cal.polygon_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as polygon_tx_ma, avg(cal.polygon_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as polygon_fee_ma, polygon.tx as polygon_tx, polygon.user as polygon_user, polygon.fee as polygon_fee, polygon.price as polygon_price, polygon.fee_usd as polygon_fee_usd, avg(polygon.tx/polygon.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as polygon_tx_per_user_ma, avg(polygon.fee_usd/polygon.user) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as polygon_fee_per_user_ma, avg(polygon.fee_usd/polygon.tx) over(ORDER BY eth.date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as polygon_fee_per_tx_ma, -- stark avg(cal.stark_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as stark_tx_ma, stark.tx as stark_tx -- sol -- avg(cal.sol_user_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as sol_user_ma, -- avg(cal.sol_tx_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as sol_tx_ma, -- avg(cal.sol_fee_pct) over(ORDER BY eth.date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)as sol_fee_ma, -- sol.tx as sol_tx, sol.user as sol_user, sol.fee1 as sol_fee, sol.price as sol_price, sol.fee_usd as sol_fee_usd FROM eth left join cal on eth.date = cal.date left join arb on eth.date = arb.date left join avax on eth.date = avax.date left join bsc on eth.date = bsc.date left join axelar on eth.date = axelar.date left join flow on eth.date = flow.date left join gnosis on eth.date = gnosis.date left join near on eth.date = near.date left join op on eth.date = op.date left join osmos on eth.date = osmos.date left join polygon on eth.date = polygon.date left join stark on eth.date = stark.date -- left join sol on eth.date = sol.date

xcheng123