with actions as

with arbitrum_actions as

(

WITH daily_token_transfers AS (

-- Step 1: Summarize daily token transfers for the G3 token by 'to_address'

SELECT

block_timestamp::date AS transfer_date,

to_address, -- Recipient address

SUM(amount)::FLOAT AS total_amount -- Total amount of tokens transferred to the address on the given date

FROM arbitrum.core.ez_token_transfers

WHERE 1=1

AND block_timestamp >= '2024-09-15' -- Filter transactions starting from September 15, 2024

AND contract_address = LOWER('0xc24A365A870821EB83Fd216c9596eDD89479d8d7') -- G3 token address on Arbitrum

GROUP BY transfer_date, to_address

ORDER BY total_amount DESC

)

-- Step 2: Join the daily token transfers with label data from the 'dim_labels' and 'dim_contracts' tables

SELECT

dtt.transfer_date,

dtt.to_address AS recipient_address,

dtt.total_amount::FLOAT AS transfer_amount,

COALESCE(dl.project_name, dc.name) AS associated_project, -- Use project name if available, else contract name

dl.label_type, -- Type of label (e.g., DEX, CEX, etc.)

dc.creator_address -- Contract creator address (if applicable)

FROM daily_token_transfers dtt

LEFT JOIN arbitrum.core.dim_labels dl ON dtt.to_address = dl.address -- Left join with labels table

LEFT JOIN arbitrum.core.dim_contracts dc ON dtt.to_address = dc.address -- Left join with contracts table