with user_txs as (
select
TX_FROM as user_id,
date_trunc('week', block_timestamp) as week,
date_trunc('day', block_timestamp) as day
from
osmosis.core.fact_transactions
where
day < '2022-10-17'
), user_weekly_txns as (
select
week,
user_id,
count(distinct day) as cnt
from
user_txs
group by
1, 2
having
cnt >= 3
), user_trans as (
select
TRADER as user_id,
date_trunc('week', block_timestamp) as week,
date_trunc('day', block_timestamp) as day,
a.label as from_token,
b.label as to_token
from
osmosis.core.fact_swaps -- a join tx_types b on a.tx_id=b.tx_id
LEFT JOIN osmosis.core.dim_labels a ON from_currency = a.address
LEFT JOIN osmosis.core.dim_labels b ON to_currency = b.address
join user_weekly_txns c on week=c.week and user_id=c.user_id
where
day < '2022-10-17'
)
select