adambalaUntitled Query
    Updated 2022-08-02
    with a as
    (
    select ORIGIN_FROM_ADDRESS ,TX_HASH bridge_tx,BLOCK_TIMESTAMP bridge_date,AMOUNT_USD volume ,AMOUNT volume_eth
    from ethereum.core.ez_eth_transfers
    where
    ORIGIN_TO_ADDRESS ='0xa0c68c638235ee32657e8f720a23cec1bfc77c77' and
    ORIGIN_FUNCTION_SIGNATURE ='0x4faa8a26' and
    ETH_FROM_ADDRESS='0xa0c68c638235ee32657e8f720a23cec1bfc77c77' and
    ETH_TO_ADDRESS ='0x8484ef722627bf18ca5ae6bcf031c23e6e922b30' and BLOCK_TIMESTAMP ::date >CURRENT_DATE -60

    ),
    b as (select distinct ORIGIN_FROM_ADDRESS,tx_id,AMOUNT_USD,BLOCK_TIMESTAMP ,TO_LABEL ,TO_LABEL_SUBTYPE ,TO_LABEL_TYPE
    from
    (select
    distinct ORIGIN_FROM_ADDRESS,tx_id,bridge_tx,AMOUNT_USD,BLOCK_TIMESTAMP ,bridge_date,(datediff('day',bridge_date,BLOCK_TIMESTAMP)) as diff ,TO_LABEL ,TO_LABEL_TYPE ,TO_LABEL_SUBTYPE
    from flipside_prod_db.polygon.udm_events inner join a on ORIGIN_FROM_ADDRESS=ORIGIN_ADDRESS
    where bridge_date < BLOCK_TIMESTAMP and diff< 7 and TO_LABEL is not null and TO_LABEL_SUBTYPE is not null and TO_LABEL_TYPE is not null
    order by 3 desc)
    )
    SELECT count(distinct ORIGIN_FROM_ADDRESS ) as users ,COUNT(DISTINCT tx_id) AS tx_ids ,sum(AMOUNT_USD) as volume , BLOCK_TIMESTAMP::DATE AS DATE ,TO_LABEL ,TO_LABEL_SUBTYPE ,TO_LABEL_TYPE
    FROM
    b where BLOCK_TIMESTAMP::date > '2022-03-01'
    GROUP BY 4,5,6,7
    Run a query to Download Data