Moesqsq2 bsc amt path
    Updated 2023-02-14

    with base as (SELECT
    'BSC' as source
    ,*,
    try_hex_decode_string(substr(data, 3 + (64*7), 16)) as destination,
    concat (source,'->',destination) as direction
    FROM BSC.core.fact_event_logs
    WHERE tx_hash in (
    SELECT tx_hash
    FROM BSC.core.fact_token_transfers
    WHERE to_address ILIKE ('0xce16f69375520ab01377ce7b88f5ba8c48f8d666')
    ) and BLOCK_TIMESTAMP >= CURRENT_DATE - 10
    and topics[0] = '0x999d431b58761213cf53af96262b67a069cbd963499fd8effd1e21556217b841'
    and destination is not null
    ),
    raw as (SELECT
    tx_hash ,
    RAW_AMOUNT / 1e6 as AMOUNT
    FROM BSC.core.fact_token_transfers
    WHERE tx_hash in (
    SELECT tx_hash
    FROM BSC.core.fact_token_transfers

    WHERE to_address ilike '0xce16f69375520ab01377ce7b88f5ba8c48f8d666')
    and contract_address LIKE lower('0x4268B8F0B87b6Eae5d897996E6b845ddbD99Adf3'))

    select
    date(b.BLOCK_TIMESTAMP) as date ,
    direction,
    sum(AMOUNT ) as USD_AMOUNT,
    avg(AMOUNT ) as avg_USD_AMOUNT,
    count(distinct b.ORIGIN_FROM_ADDRESS) as users,
    count(distinct b.TX_HASH) as txns
    from
    base b , raw r
    Run a query to Download Data