WITH Donations AS ( SELECT cliente_id AS Carteira, SUM( CASE WHEN tipo = 'entrada' THEN valor ELSE - valor END ) AS donation FROM ( SELECT TX_FROM AS cliente_id, 'entrada' AS tipo, SUM(AMOUNT) AS valor FROM solana.core.fact_transfers WHERE TX_TO = 'BUrwAobZ9bgJsza3SBNF6Mq2jpdCo9SyiqR2FDVfd5PQ' AND BLOCK_TIMESTAMP >= '2024-03-21 00:00' AND BLOCK_TIMESTAMP < '2024-03-25 10:05' AND MINT IN ( 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB', 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' ) GROUP BY TX_FROM UNION ALL SELECT TX_TO AS cliente_id, 'saida' AS tipo, SUM(AMOUNT) AS valor FROM solana.core.fact_transfers WHERE TX_FROM = 'BUrwAobZ9bgJsza3SBNF6Mq2jpdCo9SyiqR2FDVfd5PQ' AND BLOCK_TIMESTAMP >= '2024-03-21 00:00' AND BLOCK_TIMESTAMP < '2024-03-25 10:05' AND MINT IN ( 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB', 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' ) GROUP BY TX_TO ) AS transacoes GROUP BY cliente_id ), Ranges AS ( SELECT CASE WHEN donation > 0 AND donation <= 1000 THEN '1 - 1000' WHEN donation > 1000 AND donation <= 2000 THEN '1001 - 2000' WHEN donation > 2000 AND donation <= 3000 THEN '2001 - 3000' WHEN donation > 3000 AND donation <= 4000 THEN '3001 - 4000' WHEN donation > 4000 AND donation <= 5000 THEN '4001 - 5000' WHEN donation > 5000 THEN '5001 ou mais' END AS Donation_Range, donation, COUNT(*) OVER (PARTITION BY CASE WHEN donation > 0 AND donation <= 1000 THEN '1 - 1000' WHEN donation > 1000 AND donation <= 2000 THEN '1001 - 2000' WHEN donation > 2000 AND donation <= 3000 THEN '2001 - 3000' WHEN donation > 3000 AND donation <= 4000 THEN '3001 - 4000' WHEN donation > 4000 AND donation <= 5000 THEN '4001 - 5000' WHEN donation > 5000 THEN '5001 ou mais' END ) AS Total_in_Range FROM Donations ) SELECT Donation_Range as "Valor da doação $", COUNT(*) AS "Numero de doadores", SUM(donation) AS "Total doado $" FROM Ranges GROUP BY Donation_Range ORDER BY MIN(donation);

Cofre da Gratidão 2024