WITH token_flow AS (SELECT DATE(block_timestamp) as "Date", to_address as "Address", token_symbol as "Token", raw_amount / (POWER(10,decimals)) as "Amount" FROM mdao_harmony.transfers LEFT JOIN mdao_harmony.tokens ON token_address = contract_address UNION ALL SELECT DATE(block_timestamp) as "Date", from_address as "Address", token_symbol as "Token", -1 * raw_amount / (POWER(10,decimals)) as "Amount" FROM mdao_harmony.transfers LEFT JOIN mdao_harmony.tokens ON token_address = contract_address ), temp AS (SELECT "Date", "Address", "Token" FROM token_flow GROUP BY "Date","Address","Token" HAVING SUM("Amount") > 0 ) SELECT DATE(GETDATE()) as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow UNION ALL SELECT DATE(GETDATE()) - 1 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 1 UNION ALL SELECT DATE(GETDATE()) - 2 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 2 UNION ALL SELECT DATE(GETDATE()) - 3 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 3 UNION ALL SELECT DATE(GETDATE()) - 4 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 4 UNION ALL SELECT DATE(GETDATE()) - 5 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 5 UNION ALL SELECT DATE(GETDATE()) - 6 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 6 UNION ALL SELECT DATE(GETDATE()) - 7 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 7 UNION ALL SELECT DATE(GETDATE()) - 8 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 8 UNION ALL SELECT DATE(GETDATE()) - 9 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 9 UNION ALL SELECT DATE(GETDATE()) - 10 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 10 UNION ALL SELECT DATE(GETDATE()) - 11 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 11 UNION ALL SELECT DATE(GETDATE()) - 12 as "Dates", COUNT(DISTINCT "Address") as "# of Addresses" FROM token_flow WHERE "Date" < DATE(GETDATE()) - 12 /* date_agg AS( SELECT "Date", "Address", "Token", SUM("Amount") AS "Amount", CONCAT("Address","Token") AS "Cat" FROM token_flow GROUP BY "Date", "Address", "Token","Cat" ), temp AS(SELECT "Date", "Address", "Token", SUM("Amount") OVER (partition by "Cat" order by "Date") as cumulative FROM date_agg ORDER BY "Date" DESC ) SELECT "Date", COUNT(DISTINCT("Address")) AS "Wallets with Balances Greater than Zero" FROM temp WHERE cumulative > 0 GROUP BY "Date" ORDER BY "Date" DESC */

Number of Wallets With Balances Greater than Zero