Context: ""You're interested in analyzing the transaction activity of a specific token address over the past month. Use our core.ez_token_transfers table to filter transactions related to the Token, and calculate the total transaction volume or number of unique users that have interaction with this token."
Query: Lets Take a look at Transfer activity of a Token on the Ethereum chain in the last 30 days. the goal here is to count number of users who Transferred this token over last 30 days or 1 month.
SELECT
date_trunc('day', block_timestamp) as day,
count(DISTINCT origin_from_address) as users
FROM ethereum.core.ez_token_transfers
WHERE contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' --- USDC contract
AND block_timestamp >= current_date - interval '1 month'
GROUP BY day
Query Syntax:
- date_trunc = Truncates a DATE, TIME, or TIMESTAMP to the specified precision.
- count = Returns either the number of non-NULL records for the specified columns or the total number of records.
- distinct = to avoid repetitive results.
- current_date = Returns the current date of the system You can use interval constants to add or subtract a period of time to/from a date, time, or timestamp
- group by day = Groups rows with the same group-by-item expressions and computes aggregate functions for the resulting group
we used column name as a group by expression