campList of destinations of users after bridging
    Updated 2022-08-01
    WITH bridge_tx AS (
    SELECT block_timestamp AS bridge_date, origin_from_address as bridger
    FROM ethereum.core.fact_event_logs
    WHERE origin_to_address = LOWER ('0xA0c68C638235ee32657e8f720a23ceC1bFc77C77')
    AND origin_function_signature IN ('0x4faa8a26' , '0xe3dec8fb')
    ),
    tx_time AS (
    SELECT bridger, MIN(block_timestamp) AS blocktime
    FROM polygon.core.fact_transactions a JOIN bridge_tx b ON a.from_address = b.bridger
    WHERE block_timestamp >= bridge_date
    GROUP BY 1
    ),
    tx_data AS (
    SELECT from_address, block_timestamp, tx_hash, to_address
    FROM polygon.core.fact_transactions
    ),
    first_tx AS(
    SELECT bridger, blocktime, tx_hash, to_address
    FROM tx_time a JOIN tx_data b ON a.blocktime = b.block_timestamp AND a.bridger=b.from_address
    ),
    final_data AS (
    select bridger,
    blocktime,
    tx_hash,
    to_address,
    project_name,
    label_type
    FROM first_tx a
    JOIN polygon.core.dim_labels b ON a.to_address = b.address
    WHERE NOT address_name LIKE '%canonical%'
    ),
    top10 as(
    SELECT project_name as "Project Name", label_type as "Type of Actions", COUNT(bridger) as "Number of bridgers" FROM final_data
    GROUP BY 1,2
    ORDER BY 3 DESC
    Run a query to Download Data