DATE_TRUNC('{{period}}',block_timestamp) as date,

COUNT(DISTINCT TX_HASH) as number_transactions,

COUNT(DISTINCT from_address) as unique_active_users,

number_transactions/unique_active_users as txns_per_user,

SUM(TX_FEE) as transaction_fees,

transaction_fees/unique_active_users as fees_per_user,

transaction_fees/number_transactions as fees_per_txn,

CASE

WHEN to_address = '0x1e04c33cd5a015e1ced0e3ecd8bdc42902512124' THEN 'Optimism Subscriber NFTs (OPNFT)'

WHEN to_address = '0x7f5c764cbc14f9669b88837ca1490cca17c31607' THEN 'USD Coin (USDC)'

WHEN to_address = '0x2e42f214467f647fe687fd9a2bf3baddfa737465' THEN '0x2e42f214467f647fe687fd9a2bf3baddfa737465'

WHEN to_address = '0x4200000000000000000000000000000000000042' THEN 'Optimism (OP)'

WHEN to_address = '0x4200000000000000000000000000000000000006' THEN 'Wrapped Ether (WETH)'

WHEN to_address = '0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45' THEN 'Uniswap V3 Router'

WHEN to_address = '0x79bc8bd53244bc8a9c8c27509a2d573650a83373' THEN 'PoolTogether V4 YieldSourcePrizePool'

WHEN to_address = '0xd5a8f233cbddb40368d55c3320644fb36e597002' THEN 'Pika Perp V3'

-- WHEN transactions.to_address=labels.address THEN labels.address_name -- UNCOMMENT THIS *

ELSE 'Other Activity'

END AS label

FROM optimism.core.fact_transactions as transactions

-- LEFT JOIN (SELECT DISTINCT address, address_name FROM optimism.core.dim_labels) as labels ON lower(transactions.to_address)=lower(labels.address) -- AND THIS ** for more labels

WHERE block_timestamp::date > '2022-09-01'

AND block_timestamp::date < CURRENT_DATE -- today's date is never complete. Please stop showing it, people :)

GROUP BY date, label

ORDER BY date DESC