shadilRand Gallery NFT Sales - daily sale
    Updated 2022-05-03
    with algo_prices as (
    SELECT date(BLOCK_HOUR) as pdate, avg(price_usd) as price
    from algorand.prices_swap
    where asset_id = 0
    GROUP by pdate
    )
    SELECT date, sum(total_algo) as total_algo, sum(total_usd) as total_usd, COUNT(*) as sales_count
    FROM (
    SELECT f.tx_group_id, sum(m.amount) as t_amount , avg(f.amount) as fam, t_amount + fam as total_algo,
    date(m.block_timestamp) as date,
    total_algo * p.price as total_usd
    from algorand.payment_transaction f
    join algorand.payment_transaction m on f.tx_group_id = m.tx_group_id and m.amount > 0.005 and lower(m.receiver) != lower('RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE')
    join algo_prices p on p.pdate = date(m.block_timestamp)
    -- where f.tx_group_id = 'l44U5hi3bcVaIAXBJbR7TGbQobCZLzAoiK+JtgGzNAw=' -- sample NFT Sale
    where lower(f.receiver) = lower('RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE')
    GROUP by date, p.price, f.tx_group_id
    )

    GROUP by date
    Run a query to Download Data