PapasotRand Gallery
    Updated 2022-05-12
    with txs as (select
    tx_group_id id
    from
    algorand.payment_transaction
    WHERE
    receiver = 'RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE' and TX_GROUP_ID is not null),

    fee as (select
    block_timestamp::date date,
    sum(AMOUNT) sales_volume,
    count(tx_id) sales_count
    from algorand.payment_transaction
    where
    tx_group_id in (select id from txs )
    AND
    receiver not in ('RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE')
    group by 1
    ),

    avg_price as (
    select
    date_trunc(day,block_hour) date,
    avg(price_usd) price from algorand.prices_swap where asset_id = 0 group by 1
    )

    SELECT
    f.date,
    f.sales_volume sales_volume_Algo,
    sum(sales_volume_Algo) over(order by f.date) cumulative_sales_volume_Algo,
    p.price Algo_price,
    sales_volume*Algo_price sales_volume_usd,
    sum(sales_volume_usd) over(order by f.date) cumulative_sales_volume_usd,
    f.sales_count sales_count
    from avg_price p, fee f
    where p.date = f.date group by 1,2,4,5,7
    Run a query to Download Data