/*
Twitter: @der_piper
Discrod: piper#6707
Solana - Metaplex Bot Free
Q72. Solana experienced another network downtime due to intense botting
of a NFT mint. Solana has been working on a creative solution to deal with
this botting issue called Quic, but Metaplex, the infrastructure protocol
for Solana NFTs decided to implement a ""bot tax"" to help alleviate
this issue.
*/
WITH all_bot_penalties AS (
SELECT
block_timestamp,
tx_id
FROM
solana.fact_transactions
WHERE
block_timestamp BETWEEN '2022-04-28' AND '2022-05-08'
AND
CAST(log_messages as string) ILIKE '%Candy Machine Botting is taxed at%'
)
SELECT
a.block_timestamp::date as date,
count(a.tx_id) as "Number of Penalties",
sum(a.mint_price) as "Amount of Penalties"
FROM
solana.fact_nft_mints a
right JOIN
all_bot_penalties b
ON
a.tx_id = b.tx_id
WHERE