How to Find the Reserves of a Specific Liquidity Pool of an Exchange

Introduction

Welcome to our in-depth guide on leveraging RBL Net, BigQuery, and Google Cloud. In this tutorial, we're diving into how you can access and analyze the detailed reserves of a particular liquidity pool within an exchange, using Pangolin as our example. You'll learn to precisely query the assets and USD value pooled in a specific liquidity pool.

Before you start

Let’s start by ensuring our Google Cloud Project is 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.

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

Steps to follow

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

CODE

-- WITH clause that sets the desired 'pair/pool'
WITH pair AS (
    SELECT
        pair_address, 
        exchange,
        token0_address, 
        token1_address,
        t0.decimals as token0_decimals, -- Fetching decimals for token0
        t1.decimals as token1_decimals, -- Fetching decimals for token1
        t0.symbol as token0_symbol,     -- Fetching symbol for token0
        t1.symbol as token1_symbol,     -- Fetching symbol for token1
        t0.total_supply as token0_supply,
        t1.total_supply as token1_supply
    FROM pairs p
    INNER JOIN tokens t0 ON p.token0_address = t0.address -- Joining with tokens table for token0
    INNER JOIN tokens t1 ON p.token1_address = t1.address -- Joining with tokens table for token1
    WHERE
        p.exchange = 'traderjoe' --Defining the desired Exchange
        AND p.pair_address = '0x3fe9552a2E06C0deB6507C7818524596FA516bfA' --Choosing the desired pair by address.
), 
prices_buy as (
    -- Subquery to fetch latest buy price data
    SELECT
        pair.pair_address,
        pair.exchange,
        quote_token,
        usd_price,
        direction
    FROM dex_swaps_with_prices prices
    JOIN pair ON
        prices.exchange = pair.exchange
        AND prices.pair_address = pair.pair_address
        AND prices.direction = 'buy' -- Focusing on 'buy' direction
    ORDER BY block_time DESC
    LIMIT 1 -- Limiting to the most recent record
), 
prices_sell as (
    -- Subquery to fetch latest sell price data
    SELECT
        pair.pair_address,
        pair.exchange,
        quote_token,
        usd_price,
        direction
    FROM dex_swaps_with_prices prices
    JOIN pair ON
        prices.exchange = pair.exchange
        AND prices.pair_address = pair.pair_address
        AND prices.direction = 'sell' -- Focusing on 'sell' direction
    ORDER BY block_time DESC
    LIMIT 1 -- Limiting to the most recent record
), 
reserves as (
    -- Subquery to calculate reserves in USD
    SELECT 
        s.pair_address, 
        s.exchange, 
        s.token0_address, 
        s.token1_address, 
        s.block_token0_reserves, 
        s.block_token0_reserves * (CASE WHEN s.token0_address = prices_buy.quote_token THEN prices_buy.usd_price ELSE prices_sell.usd_price END) as reserves_token0_USD,
        s.block_token1_reserves,
        s.block_token1_reserves * (CASE WHEN s.token1_address = prices_buy.quote_token THEN prices_buy.usd_price ELSE prices_sell.usd_price END) as reserves_token1_USD
    FROM 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 block_time desc
    LIMIT 1 -- Limiting to the most recent record
)

-- Final select to fetch data from the 'reserves' subquery
SELECT * FROM reserves

After these, we can visualize your data in a spreadsheet by clicking on “Explore Data” and then on “Explore with Sheet”

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

Thanks for reading through the tutorial, hope this was of help. If you have any questions make sure to contact us for more help.