adambalaUntitled Query
    Updated 2022-09-16
    with bridge_erc20_to_near as (
    select el.block_timestamp::date as date,
    tx_hash,
    event_inputs:accountId as receiver,
    event_inputs:sender as sender,
    event_inputs:token as token_address,
    symbol,
    (event_inputs:amount)/pow(10, decimals) as amount
    from ethereum.core.fact_event_logs el
    left join ethereum.core.dim_contracts_extended ec on el.event_inputs:token=ec.contract_address
    where ORIGIN_TO_ADDRESS = '0x23ddd3e3692d1861ed57ede224608875809e127f' -- Near: Rainbow bridge
    and CONTRACT_NAME = 'ERC20Locker'
    and EVENT_NAME = 'Locked'
    and ORIGIN_FUNCTION_SIGNATURE = '0x0889bfe7'
    and symbol is not null
    and receiver like '%.near'
    ), erc20_prices as (
    select hour::date as date, symbol, token_address, avg(price) as price
    from ethereum.core.fact_hourly_token_prices
    where token_address in (select distinct token_address from bridge_erc20_to_near)
    and date >= '2021-03-15'
    group by 1,2,3
    ), near_prices as (
    with swaps as (
    select
    block_timestamp::date as date,
    logs[0] as log,
    substring(log, 1, CHARINDEX(' wrap.near for', log)) as first_part,
    regexp_replace(first_part, '[^0-9]', '')/pow(10, 24) as near_amount,
    substring(log, CHARINDEX('for', log), 100) as second_part,
    substring(second_part, 1, CHARINDEX('dac', second_part)-2) as second_part_amount,
    regexp_replace(second_part_amount, '[^0-9]', '')/pow(10,6) as usdt_amount
    from flipside_prod_db.mdao_near.receipts
    where logs[0] like 'Swapped % wrap.near for % dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near'
    and date >= '2021-03-15'
    Run a query to Download Data