Afonso_Diaz2023-08-25 07:59 PM
    Updated 2023-08-25
    with

    pricet as (
    select
    hour::date as date,
    token_address,
    avg(price) as usdprice
    from ethereum.core.fact_hourly_token_prices
    group by 1,2
    )
    ,

    main as (
    select
    tX_hash,
    block_timestamp,
    symbol,
    amount_paid*usdprice as usd_amount,
    payer as user
    from ethereum.maker.ez_repayments
    join pricet on trunc(block_timestamp,'day')=pricet.date and token_paid=token_address
    where TX_STATUS = 'SUCCESS'
    )

    select
    count(distinct tx_hash) as txns,
    count(distinct user) as users,
    sum(usd_amount) as usd_volume,
    avg(usd_amount) as avg_usd_volume,
    median(usd_amount) as median_usd_volume
    from main

    Run a query to Download Data