SELECT

block_timestamp,

origin_from_address,

tx_hash

-- MIN(block_timestamp) OVER (PARTITION BY origin_from_address) AS initial_timestamp,

-- MAX(block_timestamp) OVER (PARTITION BY origin_from_address) AS last_timestamp

FROM {{chain}}.core.ez_decoded_event_logs

WHERE block_timestamp::date >= '2022-10-01'

AND event_name IN ('LiFiGenericSwapCompleted','LiFiTransferStarted')

),

transactions AS (

SELECT

origin_from_address,

COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS n_days,

COUNT(DISTINCT DATE_TRUNC('week', block_timestamp)) AS n_weeks,

COUNT(DISTINCT DATE_TRUNC('month', block_timestamp)) AS n_months,

COUNT(DISTINCT tx_hash) AS n_txns

FROM users

GROUP BY origin_from_address

),

total_user_count AS (

SELECT

COUNT(DISTINCT origin_from_address) AS total_users

FROM transactions

),

user_counts AS (

SELECT

t.origin_from_address,

t.n_days,

t.n_weeks,

t.n_months,

t.n_txns,

tuc.total_users

FROM transactions t

CROSS JOIN total_user_count tuc