0xHaM-dtotal contracts-transactions
Updated 2023-04-12Copy Reference Fork
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
›
⌄
with pool_tb as (
select
date_trunc('day', block_timestamp) as date,
FROM_CURRENCY,
TO_CURRENCY,
tx_id,
'Pool_Id:' || b.value::string as "Pool ID"
from osmosis.core.fact_swaps, lateral flatten(input => pool_ids) b
)
,label_tb1 as (
select date , LABEL as from_label, TO_CURRENCY , tx_id , "Pool ID"
from pool_tb a join osmosis.core.dim_labels b on a.from_currency = b.ADDRESS
)
,label_tb2 as (
select date, from_label, label as to_label, tx_id, "Pool ID"
from label_tb1 a join osmosis.core.dim_labels b on a.TO_CURRENCY = b.ADDRESS
)
,lst_tb as (
select date, "Pool ID",tx_id, from_label || ' to ' || to_label as pair
from label_tb2
)
select
"Pool ID",
pair,
count(DISTINCT pair) as pair_count
from lst_tb
where date >= '2022-05-01'
group by 1, 2
-- ORDER by 3 DESC
-- limit 10
Run a query to Download Data