levanaAvg Volume,trades per Trader
Updated 2024-08-19
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 Volume Avg Volume per Trader @ https://flipsidecrypto.xyz/studio/queries/8a4d6e2c-8b58-47c4-b68b-f5e2201844d4
-- forked from Volume Weekly @ https://flipsidecrypto.xyz/studio/queries/6799195c-34f5-4484-846e-7a07e2f171b9
with Volume AS (
SELECT
Volume.block_timestamp,
Volume.tx_id,
Volume.attribute_value AS Volume
FROM
osmosis.core.fact_msg_attributes AS Volume
WHERE
Volume.attribute_key = 'volume-usd' and Volume.MSG_TYPE='wasm-history-trade-volume'
and Volume.tx_succeeded=TRUE and Volume.block_timestamp Between '{{START_DATE}}' and '{{END_DATE}}'),
trader AS (
SELECT
direction.block_timestamp,
direction.tx_id,
direction.attribute_value AS trader
FROM
osmosis.core.fact_msg_attributes AS direction
WHERE
direction.attribute_key = 'pos-owner' and direction.MSG_TYPE like 'wasm-position%'
and direction.tx_succeeded=TRUE and direction.block_timestamp Between '{{START_DATE}}' and '{{END_DATE}}'
),
main AS (
select
block_timestamp,
TX_ID,
trader,
Volume
from volume inner join trader using (TX_ID,block_timestamp) group by 1,2,3,4
)
SELECT
SUM(all_time_volume) / COUNT(DISTINCT trader) AS avg_volume_all_time,
SUM(last_24h_volume) / NULLIF(COUNT(DISTINCT CASE WHEN last_24h_volume > 0 THEN trader END), 0) AS avg_volume_last_24h,
QueryRunArchived: QueryRun has been archived