SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS date,

COUNT(DISTINCT TRADER) AS traders,

COUNT(*) AS trades

FROM

osmosis.core.fact_swaps

WHERE ARRAY_CONTAINS('773'::variant, POOL_IDS)

GROUP BY date

ORDER BY date

), average AS (

SELECT

AVG(traders) AS traders_average,

AVG(trades) AS trades_average

FROM result

date,

traders,

trades,

(SELECT traders_average FROM average) traders_average,

(SELECT trades_average FROM average) trades_average,

(trades_average/traders_average) * traders AS CRITERION,

CASE

WHEN trades/traders >= trades_average/traders_average THEN 'Suspicious'

-- WHEN trades >= trades_average THEN 'Suspicious'

ELSE 'Normal'

END AS type

FROM result