-- Calculate the sum of outgoings from the specified addresses over time

SELECT DATE_TRUNC('day', t.block_timestamp) AS date,

CASE

WHEN t.from_address = '0x8c02d4cc62f79aceb652321a9f8988c0f6e71e68' THEN 'Community Treasury'

WHEN t.from_address = '0x98830c37aa6abdae028bea5c587852c569092d71' THEN 'Association'

WHEN t.from_address = '0xa201d3c815ac9d4d8830fb3de2b490b5b0069aca' THEN 'Eco Inc.'

WHEN t.from_address = '0x99f98ea4a883db4692fa317070f4ad2dc94b05ce' THEN 'Association'

WHEN t.from_address = '0xa28f219bf1e15f5217b8eb5f406bcbe8f13d16dc' THEN 'Claim Contract'

ELSE 'Unknown'

END AS address_label,

CASE

WHEN t.from_address = '0x8c02d4cc62f79aceb652321a9f8988c0f6e71e68' THEN 'Community Treasury'

WHEN t.from_address = '0x98830c37aa6abdae028bea5c587852c569092d71' THEN 'Association'

WHEN t.from_address = '0xa201d3c815ac9d4d8830fb3de2b490b5b0069aca' THEN 'Eco Inc.'

WHEN t.from_address = '0x99f98ea4a883db4692fa317070f4ad2dc94b05ce' THEN 'Association'

WHEN t.from_address = '0xa28f219bf1e15f5217b8eb5f406bcbe8f13d16dc' THEN 'Claim Contract'

ELSE 'Unknown'

END AS tag,

SUM(t.raw_amount / 1e18) AS total_outgoings

FROM ethereum.core.ez_token_transfers t

WHERE t.from_address IN (

'0x8c02d4cc62f79aceb652321a9f8988c0f6e71e68',

'0x98830c37aa6abdae028bea5c587852c569092d71',

'0xa201d3c815ac9d4d8830fb3de2b490b5b0069aca',

'0x99f98ea4a883db4692fa317070f4ad2dc94b05ce',

'0xa28f219bf1e15f5217b8eb5f406bcbe8f13d16dc'

)

AND t.contract_address = '0x8dbf9a4c99580fc7fd4024ee08f3994420035727'

GROUP BY DATE_TRUNC('day', t.block_timestamp), address_label, tag

SELECT date, tag, SUM(total_outgoings) AS total_outgoings

FROM outgoings

WHERE date between '{{start_date}}' AND {{end_date}}

GROUP BY date, tag

ORDER BY date, tag;