LUNA Distribution, Mvmt II

    Question 144: Answer one of the following two questions (or both, to be eligible for the grand prize): What is the distribution of LUNA for active wallets (i.e. wallets with at least one transaction in the past 90 days? What is the distribution of LUNA if wallets that have never transacted are excluded?

    What is the distribution of LUNA for active wallets (i.e. wallets with at least one transaction in the past 90 days?

    For this bounty, we need to first filter the active wallets as the addresses that have made a transaction in the past 90 days. For that, we first create a table for Active_Wallets, using an INNER JOIN on terra.transfers and terra.daily_balances, selecting the wallets which did a transfer in the last 90 days and copying the balance of the last day available (CURRENT_DATE - 1). We then use this table to add a CASE variable ANIMALS to asign each wallet a name according to the balance held. This step also is used to eliminate duplicate addresses with a DISTINCT. Finally, a COUNT is done, grouping by ANIMALS.

    The graph below shows a well distributed amount of wallet holding LUNA, assuming more accounts holding less balance than accounts holding more balance is well distributed.

    • MOUSE 0 - 20
    • CAT 20 - 200
    • DOG 200 - 2000
    • HORSE 2000 - 20000
    • ELEPHANT 20000 - 200000
    • DINOSAUR more than 200000
    Loading...

    QUERY CODE

    ''' WITH ACTIVE_WALLETS AS( SELECT a.event_from AS ACTIVE, date_trunc('DAY', a.block_timestamp) AS FECHA, b.balance, b.balance_type FROM terra.transfers a INNER JOIN terra.daily_balances b ON a.event_from = b.address AND b.currency = 'LUNA' AND b.balance > 0 AND b.date = CURRENT_DATE - 1 WHERE a.event_amount > 0 AND FECHA >= CURRENT_DATE - 90 ), SINGLE_ADDRESS_BALANCE AS( SELECT DISTINCT(ACTIVE), BALANCE, BALANCE_TYPE, CASE WHEN (BALANCE > 0 AND BALANCE <= 20) THEN '01-MOUSE' WHEN (BALANCE > 20 AND BALANCE <= 200) THEN '02-CAT' WHEN (BALANCE > 200 AND BALANCE <= 2000) THEN '03-DOG' WHEN (BALANCE > 2000 AND BALANCE <= 20000) THEN '04-HORSE' WHEN (BALANCE > 20000 AND BALANCE <= 200000) THEN '05-ELEPHANT' WHEN (BALANCE > 200000) THEN '06-DINOSAUR' END AS ANIMALS FROM ACTIVE_WALLETS ) SELECT ANIMALS, BALANCE_TYPE, COUNT(ANIMALS) FROM SINGLE_ADDRESS_BALANCE GROUP BY ANIMALS, BALANCE_TYPE ORDER BY ANIMALS

    ACKNOWLEDGEMENT

    This bounty was done in collaboration with discord user equisL#8635, Flipside account scastellano. We discussed the best way to write the query and solved problems together. Each one did his own analysis and drew their own conclusions.