with aa as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, from_address AS user FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' UNION ALL SELECT DATE_TRUNC('DAY', block_timestamp) AS date, to_address AS user FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' AND block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' ), aa_agg as ( SELECT date, COUNT(DISTINCT user) AS metricvalue FROM aa GROUP BY date ORDER BY date ), tv as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' ), tv_agg as ( SELECT date, sum(val) AS metricvalue FROM tv GROUP BY date ORDER BY date ), cex_wallets_26_inflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('binance') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c26_inflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and to_address in ( select address from cex_wallets_26_inflow ) ), cex_wallets_26_inflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c26_inflow GROUP BY date ORDER BY date ), cex_wallets_27_inflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('okex') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c27_inflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and to_address in ( select address from cex_wallets_27_inflow ) ), cex_wallets_27_inflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c27_inflow GROUP BY date ORDER BY date ), cex_wallets_28_inflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('kucoin') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c28_inflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and to_address in ( select address from cex_wallets_28_inflow ) ), cex_wallets_28_inflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c28_inflow GROUP BY date ORDER BY date ), cex_wallets_29_inflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('crypto.com') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c29_inflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and to_address in ( select address from cex_wallets_29_inflow ) ), cex_wallets_29_inflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c29_inflow GROUP BY date ORDER BY date ), cex_wallets_30_inflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('coinbase') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c30_inflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and to_address in ( select address from cex_wallets_30_inflow ) ), cex_wallets_30_inflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c30_inflow GROUP BY date ORDER BY date ), cex_wallets_32_inflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('kraken') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c32_inflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and to_address in ( select address from cex_wallets_32_inflow ) ), cex_wallets_32_inflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c32_inflow GROUP BY date ORDER BY date ), cex_wallets_33_outflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('binance') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c33_outflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and from_address in ( select address from cex_wallets_33_outflow ) ), cex_wallets_33_outflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c33_outflow GROUP BY date ORDER BY date ), cex_wallets_34_outflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('okex') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c34_outflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and from_address in ( select address from cex_wallets_34_outflow ) ), cex_wallets_34_outflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c34_outflow GROUP BY date ORDER BY date ), cex_wallets_35_outflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('kucoin') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c35_outflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and from_address in ( select address from cex_wallets_35_outflow ) ), cex_wallets_35_outflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c35_outflow GROUP BY date ORDER BY date ), cex_wallets_36_outflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('crypto.com') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c36_outflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and from_address in ( select address from cex_wallets_36_outflow ) ), cex_wallets_36_outflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c36_outflow GROUP BY date ORDER BY date ), cex_wallets_37_outflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('coinbase') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c37_outflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and from_address in ( select address from cex_wallets_37_outflow ) ), cex_wallets_37_outflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c37_outflow GROUP BY date ORDER BY date ), cex_wallets_39_outflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('kraken') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c39_outflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and from_address in ( select address from cex_wallets_39_outflow ) ), cex_wallets_39_outflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c39_outflow GROUP BY date ORDER BY date ), cex_wallets_42_inflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('bitfinex') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c42_inflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and to_address in ( select address from cex_wallets_42_inflow ) ), cex_wallets_42_inflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c42_inflow GROUP BY date ORDER BY date ), cex_wallets_43_outflow as ( select address from crosschain.core.address_labels where blockchain in ('ethereum') and project_name in ('bitfinex') and label_subtype in ('hot_wallet') and label_type in ('cex', 'CEX') ), c43_outflow as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, ETH_VALUE AS val FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' and from_address in ( select address from cex_wallets_43_outflow ) ), cex_wallets_43_outflow_agg as ( SELECT date, sum(val) AS metricvalue FROM c43_outflow GROUP BY date ORDER BY date ), tc as ( SELECT DATE_TRUNC('DAY', block_timestamp) AS date, tx_hash AS txs FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' and block_timestamp >= '2023-07-01 00:00:00' AND block_timestamp < '2023-07-02 00:00:00' ), tc_agg as ( SELECT date, COUNT(txs) AS metricvalue FROM tc GROUP BY date ORDER BY date ) select date, metricvalue, 1 as metricid from aa_agg union all select date, metricvalue, 45 as metricid from tc_agg union all select date, metricvalue, 9 as metricid from tv_agg union all select date, metricvalue, 26 as metricid from cex_wallets_26_inflow_agg union all select date, metricvalue, 27 as metricid from cex_wallets_27_inflow_agg union all select date, metricvalue, 28 as metricid from cex_wallets_28_inflow_agg union all select date, metricvalue, 29 as metricid from cex_wallets_29_inflow_agg union all select date, metricvalue, 30 as metricid from cex_wallets_30_inflow_agg union all select date, metricvalue, 32 as metricid from cex_wallets_32_inflow_agg union all select date, metricvalue, 33 as metricid from cex_wallets_33_outflow_agg union all select date, metricvalue, 34 as metricid from cex_wallets_34_outflow_agg union all select date, metricvalue, 35 as metricid from cex_wallets_35_outflow_agg union all select date, metricvalue, 36 as metricid from cex_wallets_36_outflow_agg union all select date, metricvalue, 37 as metricid from cex_wallets_37_outflow_agg union all select date, metricvalue, 39 as metricid from cex_wallets_39_outflow_agg union all select date, metricvalue, 42 as metricid from cex_wallets_42_inflow_agg union all select date, metricvalue, 43 as metricid from cex_wallets_43_outflow_agg

ict-crd