Flipside Data ScienceBuy and stake ESE
    Updated 2024-08-26
    with buy_ese as (
    SELECT
    ds.block_timestamp,
    ds.tx_hash as tx_id,
    1 as action_count,
    LOWER(ds.origin_from_address) as address,
    TRUE as valid,
    1 as quest_step,
    'BLAST' as currency,
    amount_usd as usd_amount,
    ds.amount as raw_token_amount,
    usd_amount / ap.price as token_amount,
    tx.tx_fee as fee_amount

    FROM
    blast.core.ez_token_transfers ds
    LEFT JOIN blast.core.fact_transactions tx on ds.tx_hash = tx.tx_hash
    LEFT JOIN blast.PRICE.ez_prices_hourly ap ON ap.hour = date_trunc('hour', ds.block_timestamp)
    and ap.token_address = lower('0xb1a5700fa2358173fe465e6ea4ff52e36e88e2ad')

    WHERE
    ds.to_address = ds.origin_from_address
    and ds.origin_to_address = '0x98994a9a7a2570367554589189dc9772241650f6'
    and ds.contract_address = '0x491e6de43b55c8eae702edc263e32339da42f58c'
    -- and ds.block_timestamp :: date >= current_Date - 5
    -- and tx.block_timestamp :: date >= current_Date - 5

    and ds.origin_from_address in (:userAddresses)
    and ds.block_timestamp :: date >= :startsAt
    and tx.block_timestamp :: date >= :startsAt
    ),
    stake_ese as (
    SELECT
    ds.block_timestamp,
    ds.tx_hash as tx_id,
    1 as action_count,
    QueryRunArchived: QueryRun has been archived