WITH lps AS ( SELECT * FROM thorchain.core.fact_liquidity_actions WHERE EXTRACT(YEAR FROM block_timestamp) = {{year}} AND ( FROM_ADDRESS IN ( 'bc1qds5n8nsu9mv5v6ylcdgngu2gma47mk602daw0z', 'DMTiheDnpCTHhbWfMgnSiAazYA8VCgPWac', '0x296c92f7996f5d3dd8c32cf8995e3b07b53e2fe6', 'thor1hzrq5dparx2xesqruz7shx877ee3wxmy6xl5mw' ) OR ASSET_ADDRESS IN ( 'bc1qds5n8nsu9mv5v6ylcdgngu2gma47mk602daw0z', 'DMTiheDnpCTHhbWfMgnSiAazYA8VCgPWac', '0x296c92f7996f5d3dd8c32cf8995e3b07b53e2fe6', 'thor1hzrq5dparx2xesqruz7shx877ee3wxmy6xl5mw' ) OR TO_ADDRESS IN ( 'bc1qds5n8nsu9mv5v6ylcdgngu2gma47mk602daw0z', 'DMTiheDnpCTHhbWfMgnSiAazYA8VCgPWac', '0x296c92f7996f5d3dd8c32cf8995e3b07b53e2fe6', 'thor1hzrq5dparx2xesqruz7shx877ee3wxmy6xl5mw' ) ) ) , buys AS ( -- add_liquidity SELECT block_timestamp AS "Timestamp", 'lp_add' AS "Type", 'THOR.RUNE' AS "Sell Asset", rune_amount AS "Sell Amount", rune_amount_usd AS "Sell USD", CASE WHEN rune_amount = 0 THEN 0 ELSE rune_amount_usd/rune_amount END AS "Sell USD Price", 'LP-' || POOL_NAME AS "Buy Asset", STAKE_UNITS / 1e8 / CASE WHEN RUNE_AMOUNT <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Buy Amount", (RUNE_AMOUNT_USD + ASSET_AMOUNT_USD) / CASE WHEN RUNE_AMOUNT <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Buy USD", "Buy USD" / "Buy Amount" AS "Buy USD Price", TX_ID AS "TXID" FROM lps WHERE lp_action = 'add_liquidity' AND rune_amount <> 0 UNION -- add_liquidity SELECT block_timestamp AS "Timestamp", 'lp_add' AS "Type", POOL_NAME AS "Sell Asset", asset_amount AS "Sell Amount", asset_amount_usd AS "Sell USD", CASE WHEN asset_amount = 0 THEN 0 ELSE asset_amount_usd/asset_amount END AS "Sell USD Price", 'LP-' || POOL_NAME AS "Buy Asset", STAKE_UNITS / 1e8 / CASE WHEN RUNE_AMOUNT <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Buy Amount", (RUNE_AMOUNT_USD + ASSET_AMOUNT_USD) / CASE WHEN RUNE_AMOUNT <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Buy USD", "Buy USD" / "Buy Amount" AS "Buy USD Price", TX_ID AS "TXID" FROM lps WHERE lp_action = 'add_liquidity' AND asset_amount <> 0 ) , sells AS ( -- remove_liquidity -- add_liquidity SELECT block_timestamp AS "Timestamp", 'lp_wd' AS "Type", 'LP-' || POOL_NAME AS "Sell Asset", STAKE_UNITS / 1e8 / CASE WHEN RUNE_AMOUNT + IL_PROTECTION <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Sell Amount", (RUNE_AMOUNT_USD + ASSET_AMOUNT_USD + IL_PROTECTION_USD) / CASE WHEN RUNE_AMOUNT + IL_PROTECTION <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Sell USD", "Sell USD" / "Sell Amount" AS "Sell USD Price", 'THOR.RUNE' AS "Buy Asset", rune_amount + il_protection AS "Buy Amount", rune_amount_usd + il_protection_usd AS "Buy USD", CASE WHEN "Buy Amount" = 0 THEN 0 ELSE "Buy USD" / "Buy Amount" END AS "Buy USD Price", TX_ID AS "TXID" FROM lps WHERE lp_action = 'remove_liquidity' AND "Buy Amount" <> 0 UNION -- add_liquidity SELECT block_timestamp AS "Timestamp", 'lp_wd' AS "Type", 'LP-' || POOL_NAME AS "Sell Asset", STAKE_UNITS / 1e8 / CASE WHEN RUNE_AMOUNT + IL_PROTECTION <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Sell Amount", (RUNE_AMOUNT_USD + ASSET_AMOUNT_USD + IL_PROTECTION_USD) / CASE WHEN RUNE_AMOUNT + IL_PROTECTION <> 0 AND ASSET_AMOUNT <> 0 THEN 2 ELSE 1 END AS "Sell USD", "Sell USD" / "Sell Amount" AS "Sell USD Price", POOL_NAME AS "Buy Asset", asset_amount AS "Buy Amount", asset_amount_usd AS "Buy USD", asset_amount_usd/asset_amount AS "Buy USD Price", TX_ID AS "TXID" FROM lps WHERE lp_action = 'remove_liquidity' AND asset_amount <> 0 ) SELECT * FROM ( SELECT * FROM buys UNION SELECT * FROM sells ) AS u ORDER BY "Timestamp"

Query	Creator	Created
Year Ranged THORChain LPs thorchain dex amm	bjamba	Apr 2, 2023
Year Ranged THORChain Swaps thorchain swaps dex amm	bjamba	Apr 2, 2023

bjamba