[Table] SushiSwap Add / Remove Liquidity

[D0R11]

Hello, I'm thinking of assigning myself to this issue. I was wondering if this table is similar to UniswapV3's lp_actions table in Flipside? Thanks

[antonyip]

Hello, I'm thinking of assigning myself to this issue. I was wondering if this table is similar to UniswapV3's lp_actions table in Flipside? Thanks

Hello and welcome, Its similar to lp_actions table for sure but simpler as its closer to UniswapV2.

[D0R11]

Gotcha. I'll take this one. I assume I'll be starting on the swaps table? Are the guide questions on the other issues applicable here, too?

[D0R11]

I've been doing some tracing on the logs and txs tables for a while now and this is what I propose:

a table similar to the lp-actions table in Flipside with the following columns:

• block_timestamp
• tx_hash
• liquidity_provider
• pool_address
• pool_name
• token0
• token0_name
• token1
• token1_name
• action (add_liquidity or remove_liquidity as values)
• amount0_adjusted,
• amount1_adjusted,
• amount0_usd
• amount1_usd

Here's a sample of the query together with the accompanying CSV file of the results:

WITH RAW_TABLE AS (
SELECT l.BLOCK_TIMESTAMP, l.TX_HASH, l.NATIVE_CONTRACT_ADDRESS, lp.POOL_ADDRESS, lp.POOL_NAME, lp.TOKEN0, lp.TOKEN1,
l.EVENT_NAME, l.EVENT_INPUTS:amount0::int AS AMOUNT0_RAW, l.EVENT_INPUTS:amount1::int AS AMOUNT1_RAW
FROM logs l
JOIN liquidity_pools lp ON l.evm_contract_address=lp.pool_address
WHERE (EVENT_NAME='Mint' OR EVENT_NAME='Burn') AND EVENT_INPUTS:sender = '0x1b02da8cb0d097eb8d57a175b88c7d8b47997506' -- uniswapv2router ONE contract address
AND l.BLOCK_TIMESTAMP >= CURRENT_DATE() - INTERVAL '1 hour' ORDER BY lp.POOL_NAME
),

CLEAN_TABLE AS (SELECT rt.BLOCK_TIMESTAMP, DATE_TRUNC('day', rt.BLOCK_TIMESTAMP) AS BLOCK_DATE, rt.TX_HASH, rt.POOL_ADDRESS, rt.POOL_NAME, rt.TOKEN0, t0.TOKEN_NAME AS TOKEN0_NAME, rt.TOKEN1, t1.TOKEN_NAME AS TOKEN1_NAME,
CASE WHEN rt.EVENT_NAME='Mint' THEN 'ADD_LIQUIDITY' ELSE 'REMOVE_LIQUIDITY' END AS ACTION, 
rt.AMOUNT0_RAW/POW(10, t0.DECIMALS) AS AMOUNT0_ADJUSTED,
rt.AMOUNT1_RAW/POW(10, t1.DECIMALS) AS AMOUNT1_ADJUSTED
FROM RAW_TABLE rt 
JOIN TOKENS t0 ON rt.TOKEN0=t0.token_address
JOIN TOKENS t1 ON rt.TOKEN1=t1.token_address)

SELECT CT.BLOCK_TIMESTAMP, CT.TX_HASH, txs.FROM_ADDRESS AS LIQUIDITY_PROVIDER, 
CT.POOL_ADDRESS, CT.POOL_NAME, CT.TOKEN0, CT.TOKEN0_NAME, 
CT.TOKEN1, CT.TOKEN1_NAME, CT.ACTION, CT.AMOUNT0_ADJUSTED, CT.AMOUNT1_ADJUSTED, 
AMOUNT0_ADJUSTED*TP0.USD_PRICE AS AMOUNT0_USD,
AMOUNT1_ADJUSTED*TP1.USD_PRICE AS AMOUNT1_USD
FROM CLEAN_TABLE CT
JOIN txs ON txs.TX_HASH=CT.TX_HASH
JOIN TOKENPRICES TP0 ON TP0.TOKEN_ADDRESS=CT.TOKEN0 AND TP0.BLOCK_DATE=CT.BLOCK_DATE
JOIN TOKENPRICES TP1 ON TP1.TOKEN_ADDRESS=CT.TOKEN1 AND TP1.BLOCK_DATE=CT.BLOCK_DATE

ORDER BY BLOCK_TIMESTAMP DESC

Sushiswap add and remove liquidity table.csv

Let me know your thoughts. Thank you!

[antonyip]

Looks good!, Couple comments,

• I see you using joins on the tokenprices, maybe use left joins as if the token's do not exists it'll not appear in the table.
• Formatting needs abit of work :P https://github.com/dbt-labs/corp/blob/master/dbt_style_guide.md

[D0R11]

Looks good!, Couple comments,

• I see you using joins on the tokenprices, maybe use left joins as if the token's do not exists it'll not appear in the table.
• Formatting needs abit of work :P https://github.com/dbt-labs/corp/blob/master/dbt_style_guide.md

Will make the necessary adjustments. Thanks!

[D0R11]

Refactored the code and tested it in my machine. It works well as intended so far.

The code:

{{
    config(
        materialized='table',
        unique_key='log_id',
        tags=['core', 'sushiswap_lp_actions'],
        cluster_by=['log_id']
        )
}}

with 

events as (
    select 
        log_id,
        block_timestamp,
        date_trunc('day', block_timestamp) as block_date,
        tx_hash,
        event_index,
        native_contract_address,
        evm_contract_address,

        case 
            when event_name='Mint' 
                then 'ADD_LIQUIDITY'
            else 'REMOVE_LIQUIDITY'
        end as action,

        event_inputs:amount0::int as amount0_raw,

        event_inputs:amount1::int as amount1_raw

    from {{ ref('logs') }}
    where (event_name='Mint' or 
           event_name='Burn') 
        and event_inputs:sender = '0x1b02da8cb0d097eb8d57a175b88c7d8b47997506'
),

txs as (
    select
        tx_hash,
        from_address
    from {{ ref('txs') }}
),

liquidity_pools as (
    select *
    from {{ ref('liquidity_pools') }}
),

tokenprices as (
    select * 
    from {{ ref('tokenprices') }}
),

tokens as (
    select *
    from {{ ref('tokens') }}
),

events_liquidity_pools as (
    select 
        events.log_id,
        events.block_timestamp,
        events.block_date,
        events.tx_hash,
        liquidity_pools.*,
        events.action,
        events.amount0_raw,
        events.amount1_raw
    from events
    join liquidity_pools 
        on events.evm_contract_address=liquidity_pools.pool_address
),

token_prices_usd as (
    select 
        tokenprices.block_date,
        tokenprices.token_address,
        tokenprices.token_symbol,
        tokens.decimals,
        tokenprices.usd_price
    from tokenprices
    join tokens 
        on tokenprices.token_address=tokens.token_address
),

final_table as (
    select
        events_liquidity_pools.log_id,
        events_liquidity_pools.block_timestamp,
        events_liquidity_pools.tx_hash,
        txs.from_address as liquidity_provider,
        events_liquidity_pools.pool_address,
        events_liquidity_pools.pool_name,
        events_liquidity_pools.token0,
        token0_prices_usd.token_symbol as token0_name,
        events_liquidity_pools.token1,
        token1_prices_usd.token_symbol as token1_name,
        events_liquidity_pools.action,
        events_liquidity_pools.amount0_raw/pow(10, token0_prices_usd.decimals) 
            as amount0_adjusted,

        events_liquidity_pools.amount1_raw/pow(10, token1_prices_usd.decimals) 
            as amount1_adjusted,

        events_liquidity_pools.amount0_raw/pow(10, token0_prices_usd.decimals)*token0_prices_usd.usd_price
            as amount0_usd,

        events_liquidity_pools.amount1_raw/pow(10, token1_prices_usd.decimals)*token1_prices_usd.usd_price 
            as amount1_usd

    from events_liquidity_pools
        join txs 
            on events_liquidity_pools.tx_hash=txs.tx_hash
        -- join token prices table to token0
        left join token_prices_usd as token0_prices_usd
            on events_liquidity_pools.token0=token0_prices_usd.token_address 
                and events_liquidity_pools.block_date=token0_prices_usd.block_date
        -- join token prices table to token1
        left join token_prices_usd as token1_prices_usd
            on events_liquidity_pools.token1=token1_prices_usd.token_address 
                and events_liquidity_pools.block_date=token1_prices_usd.block_date
    order by events_liquidity_pools.block_timestamp desc
)

select * from final_table

output:

root@fa914b612299:/harmony# dbt run --select sushiswap_lp_actions
Running with dbt=0.21.1
Found 37 models, 303 tests, 0 snapshots, 0 analyses, 180 macros, 0 operations, 8 seed files, 6 sources, 0 exposures

16:14:55 | Concurrency: 4 threads (target='dev')
16:14:55 |
16:14:55 | 1 of 1 START table model DEV.sushiswap_lp_actions.................... [RUN]
16:15:53 | 1 of 1 OK created table model DEV.sushiswap_lp_actions............... [SUCCESS 1 in 57.52s]
16:15:53 | 
16:15:53 | Finished running 1 table model in 63.46s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Let me know if this is good. Thanks!

[antonyip]

Create the pr and i’ll point out the issues. You’ll also need to do the yaml. It also has to be a incremental table instead of a full table.

[D0R11]

Okay will do