-- NOTE: COMMENTED OUT QUERIES ARE NOT RUNNING DUE TO LACK OF DATA + THE QUERY RETURNING NULL ON THE AVAILABLE DATASET with bellow_1000_terra_ust as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE POOL_NAME='TERRA.UST' AND LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 GROUP BY 1 ), bellow_1000_terra_luna as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE POOL_NAME='TERRA.LUNA' AND LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 GROUP BY 1 ), all_terra as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_terra FROM thorchain.liquidity_actions WHERE POOL_NAME='TERRA.UST' OR POOL_NAME='TERRA.LUNA' AND LP_ACTION='add_liquidity' GROUP BY 1 ), terra_table as ( SELECT bellow_1000_terra_ust.date, (bellow_1000_terra_ust.count_bellow_1000 + bellow_1000_terra_luna.count_bellow_1000) as count_bellow, count_bellow/(all_terra.count_terra) *100 as pct_small_lps, 'terra' as column_name, all_terra.count_terra as total_count_terra FROM bellow_1000_terra_ust INNER JOIN all_terra ON all_terra.date=bellow_1000_terra_ust.date INNER JOIN bellow_1000_terra_luna ON bellow_1000_terra_luna.date=all_terra.date ), bellow_1000_eth_eth as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 AND POOL_NAME='ETH.ETH' GROUP BY 1 ), bellow_1000_eth_xrune as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 AND POOL_NAME='ETH.XRUNE-0X69FA0FEE221AD11012BAB0FDB45D444D3D2CE71C' GROUP BY 1 ), -- bellow_1000_eth_usdt as ( -- SELECT -- date_trunc('day', block_timestamp) as date, -- COUNT(*) as count_bellow_1000 -- FROM thorchain.liquidity_actions -- WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 -- AND POOL_NAME='ETH.USDT-0XDAC17F958D2EE523A2206206994597C13D831EC7' -- GROUP BY 1 -- ), bellow_1000_eth_usdc as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 AND POOL_NAME='ETH.USDC-0XA0B86991C6218B36C1D19D4A2E9EB0CE3606EB48' GROUP BY 1 ), -- bellow_1000_eth_thor as ( -- SELECT -- date_trunc('day', block_timestamp) as date, -- COUNT(*) as count_bellow_1000 -- FROM thorchain.liquidity_actions -- WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 -- AND POOL_NAME='ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' -- GROUP BY 1 -- ), bellow_1000_eth_snx as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 AND POOL_NAME='ETH.SNX-0XC011A73EE8576FB46F5E1C5751CA3B9FE0AF2A6F' GROUP BY 1 ), -- bellow_1000_eth_perp as ( -- SELECT -- date_trunc('day', block_timestamp) as date, -- COUNT(*) as count_bellow_1000 -- FROM thorchain.liquidity_actions -- WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 -- AND POOL_NAME='ETH.PERP-0XBC396689893D065F41BC2C6ECBEE5E008523344' -- GROUP BY 1 -- ), -- bellow_1000_eth_dai as ( -- SELECT -- date_trunc('day', block_timestamp) as date, -- COUNT(*) as count_bellow_1000 -- FROM thorchain.liquidity_actions -- WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 -- AND POOL_NAME='ETH.DAI-0X6B175474E89094C44DA98B954EEDEAC495271D0F' -- GROUP BY 1 -- ), -- bellow_1000_eth_aave as ( -- SELECT -- date_trunc('day', block_timestamp) as date, -- COUNT(*) as count_bellow_1000 -- FROM thorchain.liquidity_actions -- WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 -- AND POOL_NAME='ETH.AAVE-0X7FC66500C84A76AD7E9C93437BFC5AC33E2DDAE9' -- GROUP BY 1 -- ), all_eth as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_eth FROM thorchain.liquidity_actions WHERE POOL_NAME='ETH.ETH' OR POOL_NAME='ETH.XRUNE-0X69FA0FEE221AD11012BAB0FDB45D444D3D2CE71C' OR POOL_NAME='ETH.USDT-0XDAC17F958D2EE523A2206206994597C13D831EC7'OR POOL_NAME='ETH.USDC-0XA0B86991C6218B36C1D19D4A2E9EB0CE3606EB48' OR POOL_NAME='ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' OR POOL_NAME='ETH.SNX-0XC011A73EE8576FB46F5E1C5751CA3B9FE0AF2A6F' OR POOL_NAME='ETH.PERP-0XBC396689893D065F41BC2C6ECBEE5E0085233447' OR POOL_NAME='ETH.DAI-0X6B175474E89094C44DA98B954EEDEAC495271D0F' OR POOL_NAME='ETH.AAVE-0X7FC66500C84A76AD7E9C93437BFC5AC33E2DDAE9' AND LP_ACTION='add_liquidity' GROUP BY 1 ), eth_table as ( SELECT bellow_1000_eth_eth.date as date, (bellow_1000_eth_eth.count_bellow_1000 + bellow_1000_eth_xrune.count_bellow_1000 + bellow_1000_eth_usdc.count_bellow_1000 + bellow_1000_eth_snx.count_bellow_1000 ) as count_bellow, count_bellow/(all_eth.count_eth) *100 as pct_small_lps, 'eth' as column_name, all_eth.count_eth as total_count_eth FROM bellow_1000_eth_eth INNER JOIN bellow_1000_eth_xrune ON bellow_1000_eth_xrune.date=bellow_1000_eth_eth.date INNER JOIN all_eth ON all_eth.date=bellow_1000_eth_eth.date INNER JOIN bellow_1000_eth_usdc ON bellow_1000_eth_usdc.date=bellow_1000_eth_eth.date INNER JOIN bellow_1000_eth_snx ON bellow_1000_eth_snx.date=bellow_1000_eth_eth.date ), bellow_1000_btc as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 AND POOL_NAME='BTC.BTC' GROUP BY 1 ), bellow_1000_ltc as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 AND POOL_NAME='LTC.LTC' GROUP BY 1 ), bellow_1000_doge as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_bellow_1000 FROM thorchain.liquidity_actions WHERE LP_ACTION='add_liquidity' AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD >= 0 AND RUNE_AMOUNT_USD + ASSET_AMOUNT_USD < 1000 AND POOL_NAME='DOGE.DOGE' GROUP BY 1 ), all_btc as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_btc FROM thorchain.liquidity_actions WHERE POOL_NAME='BTC.BTC' AND LP_ACTION='add_liquidity' GROUP BY 1 ), all_ltc as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_ltc FROM thorchain.liquidity_actions WHERE POOL_NAME='LTC.LTC' AND LP_ACTION='add_liquidity' GROUP BY 1 ), all_doge as ( SELECT date_trunc('day', block_timestamp) as date, COUNT(*) as count_doge FROM thorchain.liquidity_actions WHERE POOL_NAME='DOGE.DOGE' AND LP_ACTION='add_liquidity' GROUP BY 1 ), btc_table as ( SELECT bellow_1000_btc.date as date, bellow_1000_btc.count_bellow_1000 as count_bellow, count_bellow/(all_btc.count_btc)*100 as pct_small_lps, 'btc' as column_name, all_btc.count_btc as total_count_btc FROM bellow_1000_btc INNER JOIN all_btc ON all_btc.date=bellow_1000_btc.date ), ltc_table as ( SELECT bellow_1000_ltc.date as date, bellow_1000_ltc.count_bellow_1000 as count_bellow, count_bellow/(all_ltc.count_ltc)*100 as pct_small_lps, 'ltc' as column_name, all_ltc.count_ltc as total_count_ltc FROM bellow_1000_ltc INNER JOIN all_ltc ON all_ltc.date=bellow_1000_ltc.date ), doge_table as ( SELECT bellow_1000_doge.date as date, bellow_1000_doge.count_bellow_1000 as count_bellow, count_bellow/(all_doge.count_doge)*100 as pct_small_lps, 'doge' as column_name, all_doge.count_doge as total_count_doge FROM bellow_1000_doge INNER JOIN all_doge ON all_doge.date=bellow_1000_doge.date ) SELECT COALESCE(terra_table.date,eth_table.date, btc_table.date, ltc_table.date, doge_table.date) as date, COALESCE(terra_table.pct_small_lps, eth_table.pct_small_lps, btc_table.pct_small_lps, ltc_table.pct_small_lps, doge_table.pct_small_lps) as percentage_of_small_lps, COALESCE(terra_table.column_name,eth_table.column_name, btc_table.column_name , ltc_table.column_name , doge_table.column_name ) as label_chain, COALESCE(terra_table.count_bellow, eth_table.count_bellow, btc_table.count_bellow , ltc_table.count_bellow , doge_table.count_bellow ) as total_count_bellow_1k_transactions, COALESCE(terra_table.total_count_terra,eth_table.total_count_eth, btc_table.total_count_btc , ltc_table.total_count_ltc , doge_table.total_count_doge ) as total_count_all_transactions FROM eth_table FULL OUTER JOIN terra_table ON terra_table.date=eth_table.date AND terra_table.pct_small_lps=eth_table.pct_small_lps AND terra_table.column_name=eth_table.column_name FULL OUTER JOIN btc_table ON btc_table.date=eth_table.date AND btc_table.pct_small_lps=eth_table.pct_small_lps AND btc_table.column_name=eth_table.column_name FULL OUTER JOIN ltc_table ON ltc_table.date=eth_table.date AND ltc_table.pct_small_lps=eth_table.pct_small_lps AND ltc_table.column_name=eth_table.column_name FULL OUTER JOIN doge_table ON doge_table.date=eth_table.date AND doge_table.pct_small_lps=eth_table.pct_small_lps AND doge_table.column_name=eth_table.column_name ORDER BY 3,2;

"Small" LP Actions