cryptallTotal Transactions, users, volume, avg volume on squid copy
Updated 2024-07-03
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
›
⌄
-- forked from deevhyn / Total Transactions, users, volume, avg volume on squid @ https://flipsidecrypto.xyz/deevhyn/q/kSYpWI2nQUCq/total-transactions-users-volume-avg-volume-on-squid
WITH axl_price AS (
SELECT
trunc(recorded_hour, 'day') AS Time,
AVG(price) AS usd_price
FROM osmosis.price.ez_prices
WHERE symbol = 'AXL'
GROUP BY 1
)
SELECT
COUNT(DISTINCT date_trunc('day', block_timestamp)) AS date,
COUNT(DISTINCT tx_hash) AS Transactions,
COUNT(DISTINCT sender) AS Users,
SUM(amount * axl.usd_price) AS usd_volume,
COUNT(DISTINCT tx_hash) / COUNT(DISTINCT sender) AS avg_tx_per_user,
SUM(amount * axl.usd_price) / COUNT(DISTINCT date_trunc('day', block_timestamp)) AS avg_vol_per_day
FROM
axelar.defi.ez_bridge_squid
JOIN
axl_price axl ON date_trunc('day', block_timestamp) = axl.Time
QueryRunArchived: QueryRun has been archived