LTirrellsmb raffle tickets probability calc
Updated 2023-07-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from smb raffle tickets by minter summary @ https://flipsidecrypto.xyz/edit/queries/79c9816e-abf6-4098-89b7-359c4d941370
-- forked from smb raffle ticket by minter @ https://flipsidecrypto.xyz/edit/queries/bd062abc-58a8-46e2-8da1-aa740a5610f6
-- forked from smb raffle ticket cost @ https://flipsidecrypto.xyz/edit/queries/19e46b24-0357-454b-b77a-5e746eea4a28
-- forked from smb raffle fees @ https://flipsidecrypto.xyz/edit/queries/78da776e-36e1-4f99-a68b-51571cd9e808
-- forked from smb raffle base @ https://flipsidecrypto.xyz/edit/queries/eb475bf3-6f9b-4748-ab29-5643ee894794
with
base as (
SELECT
block_timestamp,
instruction:accounts[5] as minter,
instruction:accounts[6] as mint,
-- these aren't necessary:
-- instruction:accounts[13] as collection_mint,
-- instruction:accounts[16] as update_authority,
tx_id
FROM
solana.core.fact_events
where
succeeded
and program_id = 'CSGrdwbJ5z58tLGKjjcmiNMj8bG1Zazthk3cXMrbSZoX'
and block_timestamp between '2023-06-29 18:00:00.000' and '2023-06-30 18:00:00.000'
and instruction:accounts[13] = '86GBq99Qiuq5ZRRcq33QRBT5sM8bTUAjz7owh2BaQCRH'
and array_size(inner_instruction:instructions) > 1
),
minters as (
select
-- date_trunc('hour', block_timestamp) as datetime,
minter,
count(tx_id) as tickets
FROM
base
group by
minter
),
summary as (
select