mz0111weekly report 15
    Updated 2023-01-18
    --credit to https://app.flipsidecrypto.com/dashboard/p22Vqu
    --
    with near_event AS (
    SELECT
    block_timestamp,
    tx_hash,
    receiver_id,
    replace(value, 'EVENT_JSON:') as logs_cleaned,
    split(logs_cleaned, ' ') as split_logs,
    try_parse_json(logs_cleaned):data as data_log,
    nvl(try_parse_json(logs_cleaned):event, try_parse_json(logs_cleaned):type) as event,
    case
    -- Staking NEAR
    -- when receiver_id IN ('aurora.pool.near', 'aurora.poolv1.near') and split_logs[1] = 'staking' then 'Stake NEAR (Aurora Pool)'
    -- when receiver_id NOT IN ('aurora.pool.near', 'aurora.poolv1.near') and receiver_id like '%pool%' and split_logs[1] = 'staking' then 'Stake NEAR (Other Pools)'
    when receiver_id = 'meta-pool.near' and event = 'STAKE' then 'Liquid Stake NEAR (Meta)'
    when receiver_id = 'linear-protocol.near' and event = 'stake' then 'Liquid Stake NEAR (Linear)'
    when receiver_id = 'nearx.stader-labs.near' and event = 'deposit_and_stake' then 'Liquid Stake NEAR (Stader)'
    else null end as event_action,
    -- case when event_action = 'Stake NEAR (Aurora Pool)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_aurora_stake,
    -- case when event_action = 'Stake NEAR (Other Pools)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_non_aurora_stake,
    -- stake amount
    try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) as native_stake, -- native staking pool relies on log
    try_parse_json(logs_cleaned):amount/pow(10,24) as meta_stake_amount, -- meta pool / relies on json
    data_log[0]:amount/pow(10,24) as stader_stake_amount, -- stader pool
    data_log[0]:staked_amount/pow(10,24) as linear_stake_amount, -- linear pool
    regexp_substr(status_value, 'Success') as reg_success,
    coalesce(native_stake, meta_stake_amount, stader_stake_amount, linear_stake_amount) as near_staked
    FROM near.core.fact_receipts,
    table(flatten(input =>logs))
    WHERE reg_success IS NOT NULL -- filter out successful transactions
    AND receiver_id NOT IN ('nearton-staking.near', 'xtoken.ref-finance.near')
    )
    SELECT
    date_trunc('day', block_timestamp) as date,
    count(distinct tx_hash) as "Tx Count",
    Run a query to Download Data