flipside

    NEAR -> AURORA
    CryptoIcicleNEAR -> AURORA
    Updated 2022-08-07
    -- Provide and explore key health metrics, as well as detailed data on at least 1 key project.

    -- Payout 17.123 NEAR
    -- Grand Prize 51.37 NEAR
    -- Payout Network Near
    -- Level Intermediate
    -- Difficulty Hard

    -- SQL Credit https://app.flipsidecrypto.com/velocity/queries/4cb7109e-ec22-4acc-938b-7e90730c1afd
    -- Dashbord: https://app.flipsidecrypto.com/dashboard/near-citizens-fQRyBU

    with bridges_from_near_to_aurora as (
    select *
    from flipside_prod_db.mdao_near.receipts
    where status_value:SuccessReceiptId is not null
    ), flatten_logs as ( -- // Flatten log values
    select
    block_timestamp::date as date,
    txn_hash,
    logs.value as log
    from
    bridges_from_near_to_aurora bridge,
    lateral flatten(input => bridge.logs) logs
    where log like 'Transfer % from %.near to aurora'
    ), attach_asset as (
    select date, txn_hash, log, TX_RECEIVER, tx_signer
    from flatten_logs f left join flipside_prod_db.mdao_near.transactions t using(txn_hash)
    ), explode_log as (
    select
    date,
    txn_hash,
    log,
    TX_RECEIVER as contract_in_near,
    TX_SIGNER as sender_near_address,
    case
    when contract_in_near = 'wrap.near' then 'NEAR'
    Run a query to Download Data