alirsop-4dec-03-swap_volume
Updated 2022-12-10
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 price
AS (
SELECT HOUR::DATE AS Price_date
,TOKEN_ADDRESS
,decimals
,avg(price) AS price_usd
FROM ethereum.core.fact_hourly_token_prices
GROUP BY 1
,2
,3
),
GigaSwap as(
SELECT date_trunc('day', BLOCK_TIMESTAMP)::DATE AS DATE
,FROM_Address
,COUNT(DISTINCT TX_HASH) AS "Number of TX"
,COUNT(DISTINCT FROM_ADDRESS) AS "Number of Users"
,sum(((RAW_AMOUNT / pow(10, decimals)) * price_usd)) AS "Volume"
-- ,sum("Number of TX") OVER (
-- ORDER BY DATE
-- ) AS "cumulative Number of TX"
-- ,sum("Volume") OVER (
-- ORDER BY DATE
-- ) AS "cumulative Amount"
-- ,sum("Number of Users") OVER (
-- ORDER BY DATE
-- ) AS "cumulative Number of TX"
FROM ethereum.core.fact_token_transfers GigaSwap
INNER JOIN price ON (
GigaSwap.contract_address = price.TOKEN_ADDRESS
AND GigaSwap.BLOCK_TIMESTAMP::DATE = price.price_date
)
WHERE TO_ADDRESS = '0x83249c6794bca5a77eb8c0af9f1a86e055459cea' --GigaSwap
AND FROM_ADDRESS != '0x83249c6794bca5a77eb8c0af9f1a86e055459cea'
GROUP BY 1,2
ORDER BY 1)
Run a query to Download Data