vendettaDistribution of Tokens By Their Volume of Actions Distribution of Tokens By Their Number of Users Distribution of Tokens By Their Number of Actions Share of Transactions Volume of Each Action During Past 14 Days (%Wise) Share of Users Count of Each Action During Past 14 Days (%Wise) Share of Transactions Count of Each Action During Past 14 Days (%Wise) $Volume of Transactions of Each Action During Past 14 Days Number of Transactions and Users & $Volume for Each Action During Past 14 Days
    Updated 2023-02-22
    -- forked from e242e1df-a459-4b94-8a6b-5ce19b477b08

    with prices as (
    select token_contract,
    case when token_contract = 'aurora' then 'WETH' else symbol end as symbol,
    avg(price_usd) AS price
    from near.core.fact_prices
    where timestamp::date >= CURRENT_DATE - 14
    and token_contract in ('usn','aurora','wrap.near','meta-pool.near','token.burrow.near','linear-protocol.near','6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near','a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near','dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near','2260fac5e5542a773aa44fbcfedf7c193bc2c599.factory.bridge.near','aaaaaa20d9e0e2461697782ef11675f668207961.factory.bridge.near')
    group by token_contract, symbol),

    deposits as (
    select transactions.tx_hash,
    transactions.tx_signer AS wallet,
    prices.symbol as token,
    case when token in ('NEAR', 'stNEAR','STNEAR', 'LiNEAR','LINEAR','wNEAR') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 24))
    when token in ('WETH', 'DAI', 'USN', 'AURORA') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 18))
    when token in ('wBTC','WBTC') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 8))
    when token in ('USDT.e', 'USDC.e') then (TRY_PARSE_JSON(calls.args):amount / POW(10, 6))
    else TRY_PARSE_JSON(calls.args):amount end as amount,
    (amount * prices.price) AS amount_usd
    from near.core.fact_transactions transactions join near.core.fact_actions_events_function_call calls ON transactions.tx_hash = calls.tx_hash
    join prices ON transactions.tx_receiver = prices.token_contract
    where transactions.block_timestamp::date >= CURRENT_DATE - 14
    and left(SPLIT_PART(transactions.tx:receipt[0]:outcome:status, '"', 2), 7) = 'Success'
    and calls.method_name = 'ft_transfer_call'
    and TRY_PARSE_JSON(calls.args):receiver_id = 'contract.main.burrow.near'
    and TRY_PARSE_JSON(calls.args):msg = ''),

    borrows as (
    select transactions.tx_hash,
    transactions.tx_signer as wallet,
    prices.symbol as token,
    case when token in ('NEAR', 'stNEAR','STNEAR', 'LiNEAR','LINEAR','wNEAR') then (TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0]:Borrow:amount / POW(10, 24))
    when token in ('WETH', 'DAI', 'USN', 'AURORA', 'USDC.e', 'USDT.e', 'wBTC','WBTC') then (TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0]:Borrow:amount / POW(10, 18))
    else TRY_PARSE_JSON(TRY_PARSE_JSON(calls.args):msg):Execute:actions[0]:Borrow:amount end as amount,
    Run a query to Download Data