nsa2000near new5
    Updated 2023-02-06
    with aurora_bridge AS (
    SELECT
    block_timestamp,
    tx_hash,
    regexp_substr(status_value, 'Success') as reg_success,
    logs,
    value,
    split(value, ' ') as split,
    try_to_numeric(split[1]::string) as numeric_check,
    receiver_id,
    split[3]::string as sender,
    split[5]::string as recipient
    FROM near.core.fact_receipts,
    table(flatten(input => logs))
    WHERE tx_hash IN (SELECT distinct tx_hash FROM near.core.fact_receipts WHERE receiver_Id = 'aurora')
    AND reg_success IS NOT NULL
    AND numeric_check IS NOT NULL
    AND split[0] = 'Transfer'
    AND split[2] = 'from'
    AND block_timestamp > '2023-01-22' AND block_timestamp < CURRENT_DATE
    ),
    ORACLE AS (
    SELECT
    date(timestamp) as price_date,
    token_contract as oracle_contract,
    symbol as oracle_symbol,
    avg(price_usd) as usd
    FROM near.core.fact_prices
    GROUP BY 1,2,3
    ),
    AURORA_TO_NEAR AS (
    SELECT
    block_timestamp,
    value,
    tx_hash,
    sender,
    Run a query to Download Data