0

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:

smart_contract_address date Amount
A Q1/2022 50
B Q1/2022 20
C Q2/2022 10
A Q1/2022 5
A Q2/2022 7

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:

Quarter Count
Q1/2022 2
Q2/2022 1

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
NickW
  • 8,430
  • 2
  • 6
  • 19
Chris
  • 3
  • 2
  • 1
    Hi - while you may get lucky, the chances of anyone on this forum knowing what these tables are and the data they contain is close to zero. So for anyone to help you you need to assume that no-one knows anything about your application or data and therefore provide a minimal reproducible example i.e. the relevant source tables and some sample data, the result you want to achieve based on this data and an explanation of the logic required to generate the result from the source. Please provide this information by updating your question with editable text (not, for example, pictures). – NickW Jul 04 '22 at 22:36

1 Answers1

0

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
NickW
  • 8,430
  • 2
  • 6
  • 19