Query editor
/**Amount of liquidity added and removed over time for USCD/WETH?- hints and answer- find the functions used to add or remove liquidity- a liquidity pair token (ERC20) represents liquidity in the pool- bonus points: how many liquidity providers (LPs) are active versus passive managers of their positions? Can you layer this as a type on top of the liquidity added/removed over time chart?*/with-- get mints and burnslp_mints as (-- we want all mint events where liquidity was addedSELECTdate_trunc('week', evt_block_time) as week, contract_address as pair, count(distinct tx."from") as unique_minters, sum(cast(amount0 as double)) as total_0_added, sum(cast(amount1 as double)) as total_1_addedFROM uniswap_v2_ethereum.Pair_evt_Mint as m -- we use event over call because it saves us one success filterLEFT JOIN ethereum.transactions as tx on m.evt_tx_hash = tx.hash -- we need transactions table to tell us the LP, sender in mint table is always going to be the uniswap routerWHERE contract_address = {{Pair Address}}GROUP BY 1, 2), lp_burns as (-- we want all mint events where liquidity was removedSELECTdate_trunc('week', evt_block_time) as week, contract_address as pair, count(distinct tx."from") as unique_burners, sum(cast(amount0 as double)) as total_0_removed, sum(cast(amount1 as double)) as total_1_removedFROM uniswap_v2_ethereum.Pair_evt_Burn bLEFT JOIN ethereum.transactions tx ON b.evt_tx_hash = tx.hashWHERE contract_address = {{Pair Address}}GROUP BY 1, 2), lp_combined as (-- we want to join mints and burns in one table, and need to use full outer join and COALESCE because there might be weeks with only a burn or a mintSELECTCOALESCE(m.week, b.week) as week, COALESCE(m.pair, b.pair) as pair, COALESCE(unique_minters, 0) as unique_minters, COALESCE(unique_burners, 0) as unique_burners, COALESCE(m.total_0_added, 0) as total_0_added, COALESCE(m.total_1_added, 0) as total_1_added, COALESCE(b.total_0_removed, 0) as total_0_removed, COALESCE(b.total_1_removed, 0) as total_1_removedFROM lp_mints mFULL OUTER JOIN lp_burns bON m.week = b.weekAND m.pair = b.pair)-- need to join to a complete sequence of dates so no weeks where nothing happened are missing, weeks as (with weeks_seq as (SELECT sequence((SELECT cast(min(week) as timestamp) FROM lp_combined), date_trunc('week', cast(now() as timestamp))