I am trying to display the development of new created ERC-20 smart contracts on Ethereum. For this purpose I am using the Analytics platform Dune which provides a several databases for SQL querying.

My problem is the following one:

I have a table that shows all transactions of a specific contract. Every transaction is displayed by one row of the table and contains following columns "date", "smart_contract_address"(identifier for a unique ERC20 smart contract) and other details of the transaction as "amount"

Simplified example:

I would like to count the different smart_contract_addresses per quarter. I want to make sure that every address is only counted once. After an address was counted it should be "ignored", not only if it appeared in the same quarter, but also in following ones.

For my example my expected query result would look like:

However my, query does not show my expected result. With the distinct keyword I make sure that in every quarter one address is only counted once, but will be counted again in the following quarters...

Can you tell me how I need to adjust my query that I count same addresses only once and for the quarter where they appeared for the very first time?

with everything as (
select contract_address as ca, date_trunc('quarter', evt_block_time) as time

from erc20."ERC20_evt_Transfer"
)

select time, count(distinct ca) as "Count"

from everything

group by time

CodePudding user response：

try this:

with everything as (
    select 
    contract_address as ca, 
    min(date_trunc('quarter', evt_block_time)) as time
    from erc20."ERC20_evt_Transfer"
    group by contract_address
)
select time, count(ca) as "Count"
from everything
group by time