WITH tx as (
SELECT to_char(date_trunc('week', block_timestamp), 'yyyy-mm-dd') as week, split_part(POOL_NAME, '-', 1) as pool_name, IFF(tx_id IS NULL, asset_tx_id, tx_id) as txid, rune_amount_usd + asset_amount_usd as amount_usd,
IFF(amount_usd < 1000, 'Small', 'Large') as deposit_category
FROM thorchain.liquidity_actions
WHERE lp_action = 'add_liquidity'
)
SELECT week, pool_name, COUNT(DISTINCT txid) as num_tx, SUM(amount_usd) as total_deposits, SUM(IFF(deposit_category = 'Small', amount_usd, 0)) as small_deposits,
IFF(total_deposits = 0, 0, small_deposits/total_deposits) * 100 as pct_small_deposits
FROM tx
WHERE week > '2022-01-01'
GROUP BY 1, 2
HAVING total_deposits > 10000