SELECT

DATE_TRUNC('day', block_timestamp) AS transaction_date,

from_address AS user,

COUNT(tx_hash) AS transaction_count

FROM

blast.core.fact_transactions

WHERE

block_timestamp BETWEEN '2024-07-01' AND '2024-07-31'

GROUP BY

DATE_TRUNC('day', block_timestamp), from_address

),

categorized_users AS (

SELECT

transaction_date,

user,

CASE

WHEN transaction_count = 1 THEN 'new'

ELSE 'returning'

END AS user_category

FROM

transaction_summary

),

daily_user_counts AS (

SELECT

transaction_date,

user_category,

COUNT(DISTINCT user) AS user_count

FROM

categorized_users

GROUP BY

transaction_date, user_category

),

pivoted_daily_counts AS (

SELECT

transaction_date,