How to Find the Liquidity Pool of a sSpecific Exchange

Introduction

Welcome to our tutorial on RBL Net, BigQuery, and Google Cloud. In this tutorial, we'll take you through the steps to utilise your RBL Net data and create a query to find all the Liquidity Pool of a specific exchange, in this case Pangolin.

Getting started

Let’s start by making sure we have our Google Cloud Project set up with our RBL Net data. If you don’t have one setup, please refer to HOW TO ADD RBL NET DATA TO A ANALYTICS HUB TUTORIAL.

Steps to follow

Now let’s go to the left menu, and click on BigQuery Studio and click on Compose a New Query.

Let’s go create a new query, by clicking on “+ Create New Query”

Now we can type the following to find our total liquidity pool of Pangolin

WITH pair AS (

SELEC

pair_address,

exchange,

token0_address,

t0.decimals as token0_decimals,

t1.decimals as token1_decimals

FROM `romenet.romenet_complete.avalanche_pairs` p

inner join `romenet.romenet_complete.avalanche_tokens` t0 on p.token0_address = t0.address

inner join `romenet.romenet_complete.avalanche_tokens` t1 on p.token0_address = t1.address

WHERE

p.exchange = 'pangolin'

AND p.pair_address = '0x55E01CeF5606198d1c23f8d463f69d947f998De5'

), prices_buy as (

select pair.pair_address, pair.exchange, quote_token, usd_price, direction from `romenet.romenet_complete.avalanche_dex_swaps_with_prices` prices

join pair ON

prices.exchange = pair.exchange

AND prices.pair_address = pair.pair_address

-- this table keeps two records for each swap, one considering token0 to be the quote and the other considering token1.

-- One will have 'buy' direction and the other 'sell' since a swap just trades a token for the other with no specific reference

AND prices.direction = 'buy'

ORDER BY block_time DESC

LIMIT 1

), prices_sell as (

select pair.pair_address, pair.exchange, quote_token, usd_price, direction from `romenet.romenet_complete.avalanche_dex_swaps_with_prices` prices

join pair ON

prices.exchange = pair.exchange

AND prices.pair_address = pair.pair_address

-- this table keeps two records for each swap, one considering token0 to be the quote and the other considering token1.

-- One will have 'buy' direction and the other 'sell' since a swap just trades a token for the other with no specific reference

AND prices.direction = 'sell'

ORDER BY block_time DESC

LIMIT 1

), reserves as (

SELECT

s.pair_address,

s.exchange,

s.token0_address,

s.token1_address,

POWER(CAST(s.reserve0 as INTEGER), -pair.token0_decimals) reserve0,

POWER(CAST(s.reserve0 as INTEGER), -pair.token0_decimals) * CAST((CASE WHEN s.token0_address = prices_buy.quote_token THEN prices_buy.usd_price ELSE prices_sell.usd_price END) as DECIMAL) as reserves0USD,

POWER(CAST(s.reserve1 as INTEGER), -pair.token1_decimals) reserve1,

POWER(CAST(s.reserve1 as INTEGER), -pair.token1_decimals) * CAST((CASE WHEN s.token1_address = prices_buy.quote_token THEN prices_buy.usd_price ELSE prices_sell.usd_price END) as DECIMAL) as reserves1USD,

FROM `romenet.romenet_complete.avalanche_dex_syncs` s

JOIN pair ON s.exchange = pair.exchange and s.pair_address = pair.pair_address

JOIN prices_buy ON s.exchange = prices_buy.exchange and s.pair_address = prices_buy.pair_address and prices_buy.direction = 'buy'

JOIN prices_sell ON s.exchange = prices_sell.exchange and s.pair_address = prices_sell.pair_address and prices_sell.direction = 'sell'

ORDER BY timestamp desc LIMIT 1

)

SELECT * FROM reserves

After these we can visualise your data in a spreadsheet by clicking on the “Explore Data” then on “Explore with Sheet”

And last but not least, you can also explore with Looker Studio by going to the bottom side again and clicking on “Explore Data” then on “Explore with Looking Studio”

Thanks for watching this tutorial and I hope it was helpful. [I’m name of who’s recording] and see you on the next RBL Net video!