-- forked from Hottest Addresses and Top Transactions 2024 @ https://flipsidecrypto.xyz/edit/queries/80fc608b-ddd0-4f9b-bcee-6bcf45816d26
SELECT
--date_trunc(day, block_timestamp) as Date,
transfer_type,
sender,
receiver,
COUNT(*) AS number_of_transactions,
SUM(amount/POW(10, decimal)) AS total_sent_amount,
AVG(amount/POW(10, decimal)) AS average_transaction_amount
FROM
axelar.core.fact_transfers
WHERE
TX_SUCCEEDED = TRUE
AND
DATE_PART(YEAR,BLOCK_TIMESTAMP) = 2024
GROUP BY
1,2, 3
HAVING
COUNT(*) > 100 AND AVG(amount/POW(10, decimal)) > 1000
ORDER BY
number_of_transactions DESC,
total_sent_amount DESC;
--LIMIT 1000