flipside

    alldomains
    mondovalldomains
    Updated 2023-08-15

    -- example logs
    -- bonk [ "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S invoke [1]", "Program log: Instruction: Buy", "Program log: Creating new domain 886.bonk", "Program log: Sending tokens: 6900000000000", "Program TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA invoke [2]", "Program log: Instruction: Burn", "Program TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA consumed 4678 of 168182 compute units", "Program TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA success", "Program log: Tokens burnt", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 14960 of 153885 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 12282 of 130683 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Update", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 6736 of 114887 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S consumed 94329 of 200000 compute units", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S success" ]
    -- poor [ "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S invoke [1]", "Program log: Instruction: Buy", "Program log: Creating new domain dokyo.poor", "Program log: Sending sols: 420000000", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 13406 of 155963 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 12228 of 131798 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Update", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 6825 of 115404 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S consumed 93893 of 200000 compute units", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S success" ]
    -- poor [ "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S invoke [1]", "Program log: Instruction: Buy", "Program log: Creating new domain 727.poor", "Program log: Sending tokens: 75000000000", "Program TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA invoke [2]", "Program log: Instruction: Transfer", "Program TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA consumed 4645 of 164896 compute units", "Program TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA success", "Program log: Tokens transfered", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 14906 of 148965 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 12228 of 124163 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Update", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 6793 of 107954 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S consumed 101371 of 200000 compute units", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S success" ]
    -- abc [ "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S invoke [1]", "Program log: Instruction: BuyOrExtendRenewable", "Program log: Buying or extending renewable domain bristol.abc", "Program log: Sending sols: 21000000", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 17906 of 157196 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Extend", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 12480 of 134203 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 12228 of 111457 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Update", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 6857 of 95207 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S consumed 114103 of 200000 compute units", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S success" ]
    -- moon [ "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S invoke [1]", "Program log: Instruction: BuyOrExtendRenewable", "Program log: Buying or extending renewable domain threads.moon", "Program log: Sending sols: 400000000", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program 11111111111111111111111111111111 invoke [2]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 21344 of 151345 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Extend", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 15626 of 124589 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Create", "Program 11111111111111111111111111111111 invoke [3]", "Program 11111111111111111111111111111111 success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 15555 of 97950 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK invoke [2]", "Program log: Instruction: Update", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK consumed 9954 of 78181 compute units", "Program ALTNSZ46uaAUU7XUV6awvdorLGqAsPwa9shm7h4uP2FK success", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S consumed 134605 of 200000 compute units", "Program TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S success" ]

    SELECT
    CASE
    WHEN log_messages[2] LIKE '%.bonk' THEN '.bonk'
    WHEN log_messages[2] LIKE '%.poor' THEN '.poor'
    WHEN log_messages[2] LIKE '%.abc' THEN '.abc'
    WHEN log_messages[2] LIKE '%.moon' THEN '.moon'
    ELSE NULL
    END AS domain,
    COUNT(*) AS domains_registered,
    COUNT(DISTINCT signers[0]) as users,
    date_trunc('week', t.block_timestamp) AS week
    FROM solana.core.fact_transactions t
    WHERE t.block_timestamp >= '2022-10-17'
    AND instructions[0]:programId = 'TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S'
    AND (
    (log_messages[1] LIKE '%Buy' AND (log_messages[2] LIKE '%.bonk' OR log_messages[2] LIKE '%.poor'))
    OR
    (log_messages[1] LIKE '%BuyOrExtendRenewable' AND (log_messages[2] LIKE '%.abc' OR log_messages[2] LIKE '%.moon'))
    OR log_messages[2] like 'Program log: Claiming an expirable domain%'
    )
    GROUP BY domain, week;
    Run a query to Download Data