PapasotTInyman LP daily mints with Aeneas rewards
Updated 2023-01-03
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
›
⌄
-- original query adjusted from a grand prize submission, https://app.flipsidecrypto.com/dashboard/tinyman-lp-actions-M-FbJm
-- Original author Phairot_max
with lp_id as (select date_trunc('day', block_timestamp) as date,
try_base64_decode_string(tx_message:txn:apaa[0]::string) as apaa,
case when array_size(tx_message:txn:apas) = 2 then tx_message:txn:apas[1]
when array_size(tx_message:txn:apas) = 3 then tx_message:txn:apas[2]
end as lp_id,
tx_id
from algorand.application_call_transaction
where try_base64_decode_string(tx_message:txn:apaa[0]::string) in ('mint', 'burn') and app_id in (552635992) and block_timestamp::date >= '2022-02-01'),
top_10_lp as (select lp.lp_id, acc_ass.asset_name, count(distinct lp.tx_id) as num_txs
from algorand.account_asset acc_ass
join lp_id lp on
acc_ass.asset_id = lp.lp_id
where lp.apaa = 'mint'
AND
asset_name = 'TinymanPool1.1 USDt-ALGO' OR
asset_name = 'TinymanPool1.1 USDC-USDt'
OR
asset_name = 'TinymanPool1.1 USDC-ALGO'
group by 1,2
order by num_txs desc
)
select lp.date, top.asset_name, count(distinct lp.tx_id) as num_txs
from top_10_lp top
join lp_id lp on
top.lp_id = lp.lp_id
group by 1,2
order by 1
Run a query to Download Data