Flipside Data ScienceTop Shot Pack open
Updated 2024-05-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
›
⌄
with pack_opens as (
select distinct tx_id
from flow.core.fact_events
where event_contract= 'A.0b2a3299cc857e29.TopShot'
and event_type = 'Withdraw'
and event_data:from::string = '0xe1f2a091f7bb5245'
-- and block_timestamp > current_Date - 10
and block_timestamp :: date >= :startsAt
),
t0 as (
SELECT
':questId' as quest_id,
block_timestamp,
tx_id,
1 as action_count,
lower(event_data:to::string) as address,
TRUE as valid,
1 as quest_step,
2.5 as token_amount,
0 as fee_amount,
'FLOW' as currency
FROM
flow.core.fact_events
WHERE
event_contract= 'A.0b2a3299cc857e29.TopShot'
and event_type = 'Deposit'
and tx_id in (select tx_id from pack_opens)
-- and block_timestamp > current_Date - 10
and block_timestamp :: date >= :startsAt
and lower(event_data:to::string) in (:userAddresses)
qualify row_number() over (partition by tx_id order by event_index asc) = 1
)
SELECT
quest_id,
block_timestamp,
tx_id,
QueryRunArchived: QueryRun has been archived