Re:Imperfect Union: How ConstitutionDAO didn't raise enough ...
by X6S1x6Okd1st on Sunday January 23, 2022 @12:29PM
(#30048117)
I might have the query wrong, but I am seeing 0.8% of redeemed fees were lost to transaction fees. I'm sure someone could also generate something that looks better into the effects of redeemtion size & whether people are just not redeeming because too much would be lost in txn fees.
Anyway here's the query:
```
WITH
burnt as (
SELECT
SUM(gas_used * (gas_price + priority_fee_per_gas))/1e18 as eth_burnt
FROM ethereum."transactions"
WHERE SUBSTRING("data",1,4) = '\x3015a5b5' --redeem
AND "to" = '\xd569d3cce55b71a8a3f3c418c329a66e5f714431'
AND SUBSTRING("data", 37,32) = '\x0000000000000000000000000000000000000000000000000000000000000024' --constitutiondao projectid ),
),
returned as (
SELECT
SUM(bytea2numeric(SUBSTRING("data", 101,32))/1e18) as returned
FROM ethereum."transactions"
WHERE SUBSTRING("data",1,4) = '\x3015a5b5' --redeem
AND "to" = '\xd569d3cce55b71a8a3f3c418c329a66e5f714431'
AND SUBSTRING("data", 37,32) = '\x0000000000000000000000000000000000000000000000000000000000000024' --constitutiondao projectid
AND "success"
)
SELECT (SELECT * FROM burnt)/(SELECT * FROM returned)*100
```
Anyway here's the query:
``` WITH burnt as ( SELECT SUM(gas_used * (gas_price + priority_fee_per_gas))/1e18 as eth_burnt FROM ethereum."transactions" WHERE SUBSTRING("data",1,4) = '\x3015a5b5' --redeem AND "to" = '\xd569d3cce55b71a8a3f3c418c329a66e5f714431' AND SUBSTRING("data", 37,32) = '\x0000000000000000000000000000000000000000000000000000000000000024' --constitutiondao projectid ), ), returned as ( SELECT SUM(bytea2numeric(SUBSTRING("data", 101,32))/1e18) as returned FROM ethereum."transactions" WHERE SUBSTRING("data",1,4) = '\x3015a5b5' --redeem AND "to" = '\xd569d3cce55b71a8a3f3c418c329a66e5f714431' AND SUBSTRING("data", 37,32) = '\x0000000000000000000000000000000000000000000000000000000000000024' --constitutiondao projectid AND "success" )
SELECT (SELECT * FROM burnt)/(SELECT * FROM returned)*100 ```
To be used on dune analytics: https://dune.xyz/home