-- and t.to_address = '0x841233a2f561cc7f36a0cf0777a0fe5a17b1fc45'

WITH valid_approvals AS (

SELECT

(CAST(contract_address AS VARCHAR) || '-' ||

CAST(topics[1] AS VARCHAR) || '-' ||

CAST(topics[2] AS VARCHAR)) AS connection,

DATE_TRUNC('day', INSERTED_TIMESTAMP) AS approval_day

FROM bsc.core.ez_decoded_event_logs

where topics[0] IN (

'0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925',

'0x17307eab39ab6107e8899845ad3d59bd9653f200f220920489ca2b5937696c31',

'0xc6a377bfc4eb120024a8ac08eef205be16b817020812c73223e81d1bdb9708ec'

)

AND topics[1] IS NOT NULL

AND topics[2] IS NOT NULL

AND INSERTED_TIMESTAMP <= DATEADD(DAY, -1, CURRENT_DATE)

AND INSERTED_TIMESTAMP >= DATEADD(DAY, -7, CURRENT_DATE)

), daily_distinct_connections AS (

SELECT

approval_day,

COUNT(DISTINCT connection) AS daily_unique_connections,

COUNT(connection) AS total_daily_connections

FROM valid_approvals

GROUP BY approval_day

SELECT

AVG(daily_unique_connections) AS daily_avg_distinct_connections,

AVG(total_daily_connections) AS daily_avg_connections,

AVG(total_daily_connections) - AVG(daily_unique_connections) AS updates