headitmanagerThe average holding period of each user (Ethereum&Polygon)
    Updated 2022-05-09
    with ethereum_deposite_tx_id as (select distinct TX_ID from ethereum.udm_events where EVENT_NAME='LogDeposit')
    , ethereum_withdraw_tx_id as (select distinct TX_ID from ethereum.udm_events where EVENT_NAME='LogWithdraw')
    , polygon_deposite_tx_id as (select distinct TX_ID from polygon.udm_events where EVENT_NAME='LogDeposit')
    , polygon_withdraw_tx_id as (select distinct TX_ID from polygon.udm_events where EVENT_NAME='LogWithdraw')
    , ethereum_deposit as (select BLOCK_TIMESTAMP,FROM_ADDRESS,AMOUNT_USD from ethereum.udm_events
    where TO_ADDRESS='0xf5bce5077908a1b7370b9ae04adc565ebd643966' and AMOUNT_USD::INT > 50
    and TX_ID in (select TX_ID from ethereum_deposite_tx_id) )
    , ethereum_withdraw as (select BLOCK_TIMESTAMP,FROM_ADDRESS,AMOUNT_USD from ethereum.udm_events
    where TO_ADDRESS='0xf5bce5077908a1b7370b9ae04adc565ebd643966'
    and AMOUNT_USD::INT > 50 and TX_ID in (select TX_ID from ethereum_withdraw_tx_id) )
    , polygon_deposit as (select BLOCK_TIMESTAMP,FROM_ADDRESS,AMOUNT_USD from polygon.udm_events
    where TO_ADDRESS='0x0319000133d3ada02600f0875d2cf03d442c3367' and amount_usd::INT > 50
    and TX_ID in (select TX_ID from polygon_deposite_tx_id) )
    , polygon_withdraw as (select BLOCK_TIMESTAMP,FROM_ADDRESS,AMOUNT_USD from polygon.udm_events
    where TO_ADDRESS='0x0319000133d3ada02600f0875d2cf03d442c3367'
    and AMOUNT_USD::INT > 50 and TX_ID in (select TX_ID from polygon_withdraw_tx_id)
    )
    (select 'ETHEREUM', concat(':',ethereum_withdraw.FROM_ADDRESS) as address, avg(datediff('day',ethereum_deposit.BLOCK_TIMESTAMP,ethereum_withdraw.BLOCK_TIMESTAMP)) as datediff
    from ethereum_deposit left join ethereum_withdraw on ethereum_deposit.FROM_ADDRESS = ethereum_withdraw.FROM_ADDRESS
    and ethereum_deposit.BLOCK_TIMESTAMP <= ethereum_withdraw.BLOCK_TIMESTAMP
    and ethereum_withdraw.AMOUNT_USD/ethereum_deposit.AMOUNT_USD *100 >= 50
    group by address having datediff > 0)

    UNION ALL

    (select 'POLYGON', concat(':',polygon_withdraw.FROM_ADDRESS) as address, avg(datediff('day',polygon_deposit.BLOCK_TIMESTAMP,polygon_withdraw.BLOCK_TIMESTAMP)) as datediff
    from polygon_deposit left join polygon_withdraw on polygon_deposit.FROM_ADDRESS = polygon_withdraw.FROM_ADDRESS
    and polygon_deposit.BLOCK_TIMESTAMP <= polygon_withdraw.BLOCK_TIMESTAMP
    and polygon_withdraw.AMOUNT_USD/polygon_deposit.AMOUNT_USD *100 >= 50
    group by address having datediff > 0)
    Run a query to Download Data