jackguyosmos q lp 1 copy copy
Updated 2023-04-29
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 osmos q lp 1 copy @ https://flipsidecrypto.xyz/edit/queries/6dc468f3-3dd8-4737-ad06-92a5ccffe3b2
-- forked from osmos q lp 1 @ https://flipsidecrypto.xyz/edit/queries/9befcd50-d3ff-43a5-a30f-d056cdb4049c
-- forked from osmos q swaps 1 @ https://flipsidecrypto.xyz/edit/queries/be566bd7-38f7-4702-ac82-64b58ba5d7cd
SELECT
date_trunc('day', block_timestamp) as day,
action,
-- sum(CASE
-- when not price_usd1 is NULL then (price_usd1 * to_amount) / power(10, to_decimal)
-- when not price_usd1 is NULL then (price_usd2 * to_amount) / power(10, from_decimal)
-- else 0 end
-- ) as volume_usd,
sum(amount * price_usd1 / power(10, decimal)) as volume,
count(DISTINCT tx_id) as LP_events,
count(DISTINCT LIQUIDITY_PROVIDER_ADDRESS) as LPers
-- *
FROM osmosis.core.fact_liquidity_provider_actions
LEFT outer JOIN (
SELECT
date_trunc('day', recorded_hour) as day1,
currency as c1,
symbol as s1,
median(price) as price_usd1
FROM osmosis.core.ez_prices
GROUP BY 1,2,3
) as a
on date_trunc('day', block_timestamp) = day1
AND c1 = CURRENCY
WHERE block_timestamp between '2022-10-01' and '2022-12-31'
AND not action LIKE '%lp%'
GROUP BY 1,2
Run a query to Download Data