-- forked from Billing Balances (TVL) Raw @ https://flipsidecrypto.xyz/edit/queries/b2b9dbf1-9aa0-47b0-be76-218774c9948f

select sum(balance_delta) over (order by day) as Total_Supply, *, sum(balance_delta) over (order by day) * token_price as MarketCap from (

select day

, sum(balance_delta) as balance_delta

, avg(token_price) as token_price

from (

select date_trunc('day', block_timestamp)::date as day

, block_timestamp

, raw_amount / pow(10,18) as balance_delta

, coalesce(token_price, lag(token_price) ignore nulls over (order by block_timestamp)) as token_price

, raw_amount / pow(10,18) * coalesce(token_price, lag(token_price) ignore nulls over (order by block_timestamp)) as amount_USD

, tx_hash

From ethereum.core.ez_token_transfers

where

contract_address = lower('0xc944E90C64B2c07662A292be6244BDf05Cda44a7')

and

from_address = lower('0x0000000000000000000000000000000000000000')

UNION ALL

select date_trunc('day', block_timestamp)::date as day

, block_timestamp

, raw_amount / pow(10,18) * -1 as balance_delta

, token_price

, raw_amount / pow(10,18) * token_price as amount_USD

, tx_hash

From ethereum.core.ez_token_transfers

where

contract_address = lower('0xc944E90C64B2c07662A292be6244BDf05Cda44a7')

and

to_address = lower('0x0000000000000000000000000000000000000000')

)

group by day