forgashquery 5
Updated 2022-08-30
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
›
⌄
with eth_txns as
(
SELECT
matic_value as value_matic, block_number, block_timestamp
FROM
polygon.core.fact_transactions
WHERE
matic_value > 0
AND block_timestamp > dateadd(day, -30, current_timestamp)
AND status = 'SUCCESS'
)
SELECT
SUM(CASE WHEN value_matic * price_close < 100 THEN value_matic * price_close ELSE 0 END) AS less_than_usd_100,
SUM(CASE WHEN value_matic * price_close BETWEEN 100 AND 500 THEN value_matic * price_close ELSE 0 END) AS usd_100_to_500,
SUM(CASE WHEN value_matic * price_close BETWEEN 501 AND 1000 THEN value_matic * price_close ELSE 0 END) AS usd_501_to_1000,
SUM(CASE WHEN value_matic * price_close BETWEEN 1001 AND 5000 THEN value_matic * price_close ELSE 0 END) AS usd_1001_to_5000,
SUM(CASE WHEN value_matic * price_close BETWEEN 5001 AND 10000 THEN value_matic * price_close ELSE 0 END) AS usd_5001_to_10000,
SUM(CASE WHEN value_matic * price_close BETWEEN 10001 AND 50000 THEN value_matic * price_close ELSE 0 END) AS usd_10001_to_50000,
SUM(CASE WHEN value_matic * price_close BETWEEN 50001 AND 100000 THEN value_matic * price_close ELSE 0 END) AS usd_50001_to_100000,
SUM(CASE WHEN value_matic * price_close BETWEEN 100001 AND 500000 THEN value_matic * price_close ELSE 0 END) AS usd_100001_to_500000,
SUM(CASE WHEN value_matic * price_close BETWEEN 500001 AND 1000000 THEN value_matic * price_close ELSE 0 END) AS usd_500001_to_1000000,
SUM(CASE WHEN value_matic * price_close > 1000000 THEN value_matic * price_close ELSE 0 END) AS greater_than_usd_1000000
FROM eth_txns
LEFT JOIN
(SELECT hour, price as price_close from ethereum.core.fact_hourly_token_prices
WHERE hour > dateadd(day, -30, current_timestamp)
and symbol = 'MATIC') AS prices
on date_trunc('h',eth_txns.block_timestamp) = hour
Run a query to Download Data