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
2023-02-20 11:27 PM ethereum evm	bjamba	Feb 20, 2023
Year Ranged THORChain LPs thorchain dex amm	bjamba	Apr 2, 2023
Year Ranged THORChain Swaps thorchain swaps dex amm	bjamba	Apr 2, 2023
Copy of My First Query	bjamba	Dec 1, 2021
2023-04-02 09:52 PM bsc evm binance	bjamba	Apr 3, 2023
Min Max Ethereum Stats ethereum evm	bjamba	Feb 25, 2023
2023-02-20 11:26 PM	bjamba	Feb 20, 2023
Date Ranged Ethereum Stats ethereum evm	bjamba	Feb 25, 2023

bjamba