connorhTerraswap Pools
Updated 2021-10-12
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
›
⌄
WITH pools as (
SELECT
tx_id,
block_timestamp,
event_attributes:liquidity_token_addr::STRING AS liquidity_token,
event_attributes:pair_contract_addr::STRING AS pool_address,
event_attributes:pair::STRING AS pair,
event_attributes:"0_contract_address"::STRING AS factory,
REGEXP_REPLACE(pair,'^.*-','') AS token1,
REGEXP_REPLACE(pair,'-.*$','') AS token0
FROM terra.msg_events
WHERE
event_type = 'from_contract'
--AND block_timestamp >= CURRENT_DATE - 9
AND event_attributes:"0_contract_address"::STRING = 'terra1ulgw0td86nvs4wtpsc80thv6xelk76ut7a7apj'
), parsed AS (
SELECT p.tx_id,
p.liquidity_token,
p.pool_address,
p.factory,
p.token0,
COALESCE(REGEXP_REPLACE(t0.address_name,'(token contract| )',''),token0) AS t0_address_name,
p.token1,
COALESCE(REGEXP_REPLACE(t1.address_name,'(token contract| )',''),token1) AS t1_address_name,
t0_address_name || '-' || t1_address_name || ' Terraswap LP' AS pool_name,
t0_address_name || '-' || t1_address_name || ' Terraswap LP Token' AS token_name
FROM
pools p
LEFT OUTER JOIN
terra.labels t0
ON p.token0 = t0.address
LEFT OUTER JOIN
terra.labels t1
ON p.token1 = t1.address
ORDER BY p.block_timestamp DESC
), parsed_labels AS (
Run a query to Download Data