PapasotRand Gallery
Updated 2022-05-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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