WITH inputs_oct_2023 AS ( SELECT tx_id, COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity FROM BITCOIN.CORE.FACT_INPUTS fi LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address WHERE block_timestamp >= '2023-10-24' AND block_timestamp < '2023-10-25' GROUP BY tx_id, from_entity ), transfers_oct_2023 AS ( SELECT '2023-10-24' AS transfer_date, count(distinct(from_entity)) AS n_from_groups, count(distinct(COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address))) AS n_to_groups FROM BITCOIN.CORE.FACT_OUTPUTS fo LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address LEFT JOIN inputs_oct_2023 i ON i.tx_id = fo.tx_id WHERE block_timestamp >= '2023-10-24' AND block_timestamp < '2023-10-25' AND from_entity IS NOT NULL AND from_entity != COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address) GROUP BY transfer_date ), inputs_apr_2023 AS ( SELECT tx_id, COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity FROM BITCOIN.CORE.FACT_INPUTS fi LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address WHERE block_timestamp >= '2023-04-24' AND block_timestamp < '2023-04-25' GROUP BY tx_id, from_entity ), transfers_apr_2023 AS ( SELECT '2023-04-24' AS transfer_date, count(distinct(from_entity)) AS n_from_groups, count(distinct(COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address))) AS n_to_groups FROM BITCOIN.CORE.FACT_OUTPUTS fo LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address LEFT JOIN inputs_apr_2023 i ON i.tx_id = fo.tx_id WHERE block_timestamp >= '2023-04-24' AND block_timestamp < '2023-04-25' AND from_entity IS NOT NULL AND from_entity != COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address) GROUP BY transfer_date ), inputs_oct_2022 AS ( SELECT tx_id, COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity FROM BITCOIN.CORE.FACT_INPUTS fi LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address WHERE block_timestamp >= '2022-10-24' AND block_timestamp < '2022-10-25' GROUP BY tx_id, from_entity ), transfers_oct_2022 AS ( SELECT '2022-10-24' AS transfer_date, count(distinct(from_entity)) AS n_from_groups, count(distinct(COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address))) AS n_to_groups FROM BITCOIN.CORE.FACT_OUTPUTS fo LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address LEFT JOIN inputs_oct_2022 i ON i.tx_id = fo.tx_id WHERE block_timestamp >= '2022-10-24' AND block_timestamp < '2022-10-25' AND from_entity IS NOT NULL AND from_entity != COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address) GROUP BY transfer_date ), inputs_apr_2022 AS ( SELECT tx_id, COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity FROM BITCOIN.CORE.FACT_INPUTS fi LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address WHERE block_timestamp >= '2022-04-24' AND block_timestamp < '2022-04-25' GROUP BY tx_id, from_entity ), transfers_apr_2022 AS ( SELECT '2022-04-24' AS transfer_date, count(distinct(from_entity)) AS n_from_groups, count(distinct(COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address))) AS n_to_groups FROM BITCOIN.CORE.FACT_OUTPUTS fo LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address LEFT JOIN inputs_apr_2022 i ON i.tx_id = fo.tx_id WHERE block_timestamp >= '2022-04-24' AND block_timestamp < '2022-04-25' AND from_entity IS NOT NULL AND from_entity != COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address) GROUP BY transfer_date ), inputs_oct_2021 AS ( SELECT tx_id, COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity FROM BITCOIN.CORE.FACT_INPUTS fi LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address WHERE block_timestamp >= '2021-10-24' AND block_timestamp < '2021-10-25' GROUP BY tx_id, from_entity ), transfers_oct_2021 AS ( SELECT '2021-10-24' AS transfer_date, count(distinct(from_entity)) AS n_from_groups, count(distinct(COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address))) AS n_to_groups FROM BITCOIN.CORE.FACT_OUTPUTS fo LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address LEFT JOIN inputs_oct_2021 i ON i.tx_id = fo.tx_id WHERE block_timestamp >= '2021-10-24' AND block_timestamp < '2021-10-25' AND from_entity IS NOT NULL AND from_entity != COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address) GROUP BY transfer_date ), inputs_apr_2021 AS ( SELECT tx_id, COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity FROM BITCOIN.CORE.FACT_INPUTS fi LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address WHERE block_timestamp >= '2021-04-24' AND block_timestamp < '2021-04-25' GROUP BY tx_id, from_entity ), transfers_apr_2021 AS ( SELECT '2021-04-24' AS transfer_date, count(distinct(from_entity)) AS n_from_groups, count(distinct(COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address))) AS n_to_groups FROM BITCOIN.CORE.FACT_OUTPUTS fo LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address LEFT JOIN inputs_apr_2021 i ON i.tx_id = fo.tx_id WHERE block_timestamp >= '2021-04-24' AND block_timestamp < '2021-04-25' AND from_entity IS NOT NULL AND from_entity != COALESCE(project_name, CAST(address_group AS varchar), pubkey_script_address) GROUP BY transfer_date ) SELECT *, n_to_groups / n_from_groups AS from_to_ratio FROM transfers_oct_2023 UNION SELECT *, n_to_groups / n_from_groups AS from_to_ratio FROM transfers_apr_2023 UNION SELECT *, n_to_groups / n_from_groups AS from_to_ratio FROM transfers_oct_2022 UNION SELECT *, n_to_groups / n_from_groups AS from_to_ratio FROM transfers_apr_2022 UNION SELECT *, n_to_groups / n_from_groups AS from_to_ratio FROM transfers_oct_2021 UNION SELECT *, n_to_groups / n_from_groups AS from_to_ratio FROM transfers_apr_2021

🌽 How to Get Bitcoin Transfers 🌽