SELECT

DATE_TRUNC('day', block_timestamp) AS date,

tx_hash,

decoded_log:user AS user,

Event_name,

SUM(decoded_log:amount / pow(10,18)) AS Total_Amount_eth

FROM

blast.core.ez_decoded_event_logs

WHERE

block_timestamp BETWEEN '2024-05-27' AND '2024-07-26'

AND EVENT_NAME IN ('BridgedDeposit', 'BridgedWithdrawal')

AND tx_status = 'SUCCESS'

AND Contract_address = '0x5e023c31e1d3dcd08a1b3e8c96f6ef8aa8fcacd1'

GROUP BY

date, tx_hash, decoded_log:user, Event_name

),

newuser AS (

SELECT MIN(date) AS MinDate,

user

FROM DepWith

GROUP BY user

SELECT

CASE

WHEN MinDate < '2024-06-26' THEN 'Before Airdrop'

ELSE 'After Airdrop'

END AS Period,

COUNT(DISTINCT user) AS "New User"

FROM newuser

GROUP BY Period, MinDate

ORDER BY Period, MinDate DESC;