WITH all_first_appearance AS (
SELECT
from_address,
MIN(block_timestamp) AS first_timestamp
FROM
{{network}}.core.fact_transactions
GROUP BY
from_address
),
period_first_appearance AS (
SELECT
aft.from_address,
aft.first_timestamp,
ft.to_address,
date_trunc('{{granularity}}', ft.block_timestamp) AS wk
FROM
all_first_appearance aft
JOIN
{{network}}.core.fact_transactions ft ON aft.from_address = ft.from_address AND aft.first_timestamp = ft.block_timestamp
WHERE
ft.block_timestamp > current_date() - interval '{{periods}} {{granularity}}'
),
filtered_labels AS (
SELECT
pfa.from_address,
pfa.to_address,
pfa.wk,
dl.label_type
FROM
period_first_appearance pfa
JOIN
{{network}}.core.dim_labels dl ON pfa.to_address = dl.address
LEFT JOIN
{{network}}.core.dim_labels dl2 ON pfa.from_address = dl2.address
WHERE
dl.label_type IN ('defi', 'dex', 'nft')