WITH EVM_tx_data as ( SELECT 'Ethereum' as blockchain, 1 as Llama_ID, 'ethereum' as currency, block_timestamp, tx_hash, status, from_address, gas_price FROM ethereum.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'BSC' as blockchain, 2 as Llama_ID, 'bnb' as currency, block_timestamp, tx_hash, status, from_address, gas_price FROM bsc.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Arbitrum' as blockchain, 3 as Llama_ID, 'ethereum' as currency, block_timestamp, tx_hash, status, from_address, gas_price_paid as gas_price FROM arbitrum.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Optimism' as blockchain, 4 as Llama_ID, 'ethereum' as currency, block_timestamp, tx_hash, status, from_address, gas_price FROM optimism.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Polygon' as blockchain, 5 as Llama_ID, 'matic' as currency, block_timestamp, tx_hash, status, from_address, gas_price FROM polygon.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Avalanche' as blockchain, 6 as Llama_ID, 'avax' as currency, block_timestamp, tx_hash, status, from_address, gas_price FROM avalanche.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Gnosis' as blockchain, 13 as Llama_ID, 'xdai' as currency, block_timestamp, tx_hash, status, from_address, gas_price FROM gnosis.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Aurora' as blockchain, 26 as Llama_ID, 'ethereum' as currency, block_timestamp, tx_hash, status, from_address, gas_price FROM aurora.core.fact_transactions WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' -- UNION -- SELECT 'Evmos' as blockchain, -- 37 as Llama_ID, -- 'evmos' as currency, -- block_timestamp, tx_id as tx_hash, tx_succeeded as status, tx_from as from_address, -- split(fee,'aevmos')[0]::int/1e9/gas_wanted as gas_price -- FROM evmos.core.fact_transactions -- WHERE block_timestamp >= '2023-06-01' -- AND block_timestamp < '2023-08-01' ), EVM_block_data as ( SELECT 'Ethereum' as blockchain, 1 as Llama_ID, block_timestamp, hash, parent_hash FROM ethereum.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'BSC' as blockchain, 2 as Llama_ID, block_timestamp, hash, parent_hash FROM bsc.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Arbitrum' as blockchain, 3 as Llama_ID, block_timestamp, hash, parent_hash FROM arbitrum.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Optimism' as blockchain, 4 as Llama_ID, block_timestamp, hash, parent_hash FROM optimism.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Polygon' as blockchain, 5 as Llama_ID, block_timestamp, hash, parent_hash FROM polygon.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Avalanche' as blockchain, 6 as Llama_ID, block_timestamp, hash, parent_hash FROM avalanche.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Gnosis' as blockchain, 13 as Llama_ID, block_timestamp, hash, parent_hash FROM gnosis.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' UNION SELECT 'Aurora' as blockchain, 26 as Llama_ID, block_timestamp, hash, parent_hash FROM aurora.core.fact_blocks WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-08-01' -- UNION -- SELECT 'Evmos' as blockchain, -- 37 as Llama_ID, -- block_timestamp, block_id as hash, -- lag(block_id) OVER (ORDER BY BLOCK_TIMESTAMP) parent_hash -- FROM evmos.core.fact_blocks -- WHERE block_timestamp >= '2023-06-01' -- AND block_timestamp < '2023-08-01' ), EVM_tx_data2 as ( SELECT D.*, D.gas_price*P.price as price_USD_gwei FROM EVM_tx_data D LEFT JOIN ( SELECT hour, 'ethereum' as currency, median(price) as price FROM crosschain.core.ez_hourly_prices WHERE HOUR >= '2023-06-01' AND lower(SYMBOL) like 'weth' GROUP BY 1 UNION SELECT hour, 'bnb' as currency, median(price) as price FROM crosschain.core.ez_hourly_prices WHERE HOUR >= '2023-06-01' AND lower(SYMBOL) like 'wbnb' GROUP BY 1 UNION SELECT hour, 'matic' as currency, median(price) as price FROM crosschain.core.ez_hourly_prices WHERE HOUR >= '2023-06-01' AND lower(SYMBOL) like 'wmatic' GROUP BY 1 UNION SELECT hour, 'avax' as currency, median(price) as price FROM crosschain.core.ez_hourly_prices WHERE HOUR >= '2023-06-01' AND lower(SYMBOL) like 'wavax' GROUP BY 1 UNION SELECT hour, 'xdai' as currency, median(price) as price FROM crosschain.core.ez_hourly_prices WHERE HOUR >= '2023-06-01' AND lower(SYMBOL) like 'wxdai' GROUP BY 1 UNION SELECT hour, 'evmos' as currency, median(price) as price FROM crosschain.core.ez_hourly_prices WHERE HOUR >= '2023-06-01' AND lower(SYMBOL) like 'wevmos' GROUP BY 1 ) P ON (date_trunc('HOUR',D.block_timestamp) = P.hour AND D.currency = P.currency) ), EVM_DAU as ( SELECT blockchain, Llama_ID, date_trunc('day',block_timestamp) as day, count(distinct from_address) as DAU FROM EVM_tx_data GROUP BY 1,2,3 ), EVM_DAU_monthly as ( SELECT *, LAG(DAU_avg) OVER (PARTITION BY blockchain ORDER BY month) as lag_stat, (DAU_avg - lag_stat)/ lag_stat as DAU_delta1m FROM( SELECT blockchain, Llama_ID, date_trunc('month',day) as month, median(DAU) as DAU_med, AVG(DAU) as DAU_avg, STDDEV(DAU) as DAU_STD FROM EVM_DAU GROUP BY blockchain, Llama_ID, month ) ), EVM_TPS as ( SELECT blockchain, Llama_ID, hour, TPS_hour, Tx_succ/TPH as Success_rate_hour FROM ( SELECT blockchain, Llama_ID, date_trunc('hour',block_timestamp) as hour, count(distinct tx_hash) as TPH, TPH/3600 as TPS_hour FROM EVM_tx_data GROUP BY 1,2,3 ) A NATURAL LEFT JOIN ( SELECT blockchain, Llama_ID, date_trunc('hour',block_timestamp) as hour, count(distinct tx_hash) as Tx_succ FROM EVM_tx_data WHERE status = 'SUCCESS' GROUP BY 1,2,3 ) B ), EVM_TPS_monthly as ( SELECT *, LAG(TPS_avg) OVER (PARTITION BY blockchain ORDER BY month) as lag_stat, (TPS_avg - lag_stat)/ lag_stat as TPS_delta1m, LAG(SRate_avg) OVER (PARTITION BY blockchain ORDER BY month) as lag_stat2, (SRate_avg - lag_stat2)/ lag_stat2 as SRate_delta1m FROM( SELECT blockchain, Llama_ID, date_trunc('month',hour) as month, median(TPS_hour) as TPS_med, AVG(TPS_hour) as TPS_avg, STDDEV(TPS_hour) as TPS_STD, median(Success_rate_hour) as SRate_med, AVG(Success_rate_hour) as SRate_avg, STDDEV(Success_rate_hour) as SRate_STD FROM EVM_TPS GROUP BY blockchain, Llama_ID, month ) ), EVM_gas_costs_monthly as ( SELECT *, LAG(price_USD_gwei_avg) OVER (PARTITION BY blockchain ORDER BY month) as lag_stat, (price_USD_gwei_avg - lag_stat)/ lag_stat as price_USD_gwei_delta1m FROM( SELECT blockchain, Llama_ID, date_trunc('month',block_timestamp) as month, count(distinct from_address) as monthly_users, median(price_USD_gwei) as price_USD_gwei_med, AVG(price_USD_gwei) as price_USD_gwei_avg, STDDEV(price_USD_gwei) as price_USD_gwei_STD -- ,MIN(price_USD_gwei) as price_USD_gwei_min, -- MAX(price_USD_gwei) as price_USD_gwei_max FROM EVM_tx_data2 GROUP BY blockchain, Llama_ID, month ) -- QUALIFY month >= '2023-07-01' ), EVM_block_time_monthly as ( SELECT *, LAG(block_time_avg) OVER (PARTITION BY blockchain ORDER BY month) as lag_stat, (block_time_avg - lag_stat)/ lag_stat as block_time_delta1m FROM( SELECT A.blockchain, A.Llama_ID, date_trunc('month', A.block_timestamp) as month, median(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as block_time_med, AVG(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as block_time_avg, STDDEV(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as block_time_std FROM EVM_block_data A LEFT JOIN EVM_block_data P ON (A.parent_hash = P.hash) WHERE A.block_timestamp >= '2023-06-01' AND A.block_timestamp < '2023-08-01' GROUP BY 1,2,3 ) ), EVM_stats_monthly as ( SELECT G.blockchain, G.Llama_ID, G.month, DAU_med, DAU_avg, DAU_std, DAU_delta1m, monthly_users, TPS_med, TPS_avg, TPS_std, TPS_delta1m, SRate_med, SRate_avg, SRate_std, SRate_delta1m, price_USD_gwei_med, price_USD_gwei_avg, price_USD_gwei_std, price_USD_gwei_delta1m, block_time_med, block_time_avg, block_time_std, block_time_delta1m FROM EVM_gas_costs_monthly G LEFT JOIN EVM_TPS_monthly T ON (G.blockchain = T.blockchain AND G.Llama_ID = T.Llama_ID AND G.month = T.month) LEFT JOIN EVM_DAU_monthly D ON (G.blockchain = D.blockchain AND G.Llama_ID = D.Llama_ID AND G.month = D.month) LEFT JOIN EVM_block_time_monthly B ON (G.blockchain = B.blockchain AND G.Llama_ID = B.Llama_ID AND G.month = B.month) -- WHERE G.month >= '2023-07-01' ) SELECT * FROM EVM_stats_monthly ORDER BY Llama_ID, month

elvis