Cipher009-CpUJ5kGnosis | Activity Spike copy
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
›
⌄
-- forked from KowalskiDeFi / Gnosis | Activity Spike @ https://flipsidecrypto.xyz/KowalskiDeFi/q/ahl3M9tFc790/gnosis-activity-spike
-- Written by Konstantinos or Konstan-🌲-os
--Used by Cipher009
SELECT
main.*,
transaction_fees*prices.price_usd as transaction_fees_usd,
transaction_fees_usd/main.unique_active_users as fees_per_user
FROM
(
SELECT
DATE_TRUNC('{{period}}',block_timestamp) as date,
COUNT(DISTINCT TX_HASH) as number_transactions,
COUNT(DISTINCT from_address) as unique_active_users,
COUNT(DISTINCT TX_HASH)/COUNT(DISTINCT from_address) as avg_txn_per_user,
SUM(TX_FEE) as transaction_fees
FROM gnosis.core.fact_transactions as transactions
LEFT JOIN (SELECT address, address_name FROM gnosis.core.dim_labels) as labels
ON transactions.to_address=labels.address
WHERE block_timestamp::date > '2022-09-15' -- right before identified spike
AND block_timestamp::date < CURRENT_DATE -- today's date is never complete. Please stop showing it people :)
GROUP BY date
) as main
INNER JOIN
(
SELECT
DATE_TRUNC('{{period}}',hour) as date,
AVG(PRICE) as price_usd
FROM ethereum.core.fact_hourly_token_prices
WHERE TOKEN_ADDRESS='0x6b175474e89094c44da98b954eedeac495271d0f'
GROUP BY date
) as prices
ON main.date=prices.date
ORDER BY date DESC
Run a query to Download Data