purifvaried-gold
Updated 2024-08-13
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
›
⌄
⌄
--receive kdk from lp
with kdk as (select block_timestamp, tx_hash,(utils.udf_hex_to_int(substr(substr(data, 0,66),3,66))::int)/1e18 as kdk_amount from berachain.testnet.fact_event_logs
where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
and contract_address=lower('0xfd27998fa0eaB1A6372Db14Afd4bF7c4a58C5364')
and concat('0x',substr(TOPICS[1], 27,64))=lower('0xe8BF5c8241B0C17aA3Ed75fA024654B4a2508086')
and origin_to_address=lower('0x496e305c03909ae382974caca4c580e1bf32afbe')
and ORIGIN_FUNCTION_SIGNATURE='0x5ae401dc'
)
--send wbera to kdk lp
/*select k.block_timestamp, k.tx_hash, kdk_amount, bera_amount, (bera_amount/kdk_amount)*bera_p from bera_price,kdk k
join bera b on b.tx_hash=k.tx_hash
where bera_amount!=0 and kdk_amount!=0
order by block_timestamp desc
limit 1000*/
bera_price as (
with bera_sent as (select block_timestamp, tx_hash,(utils.udf_hex_to_int(substr(substr(data, 0,66),3,66))::int)/1e18 as bera_amount from berachain.testnet.fact_event_logs
where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
and contract_address=lower('0x7507c1dc16935B82698e4C63f2746A2fCf994dF8')
and concat('0x',substr(TOPICS[2], 27,64))=lower('0xab827b1cc3535a9e549ee387a6e9c3f02f481b49')
and ORIGIN_FUNCTION_SIGNATURE='0x0b2f6f3f'),
honey_received as (select block_timestamp, tx_hash,(utils.udf_hex_to_int(substr(substr(data, 0,66),3,66))::int)/1e18 as honey_amount from berachain.testnet.fact_event_logs
where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
and contract_address=lower('0x0E4aaF1351de4c0264C5c7056Ef3777b41BD8e03')
and concat('0x',substr(TOPICS[1], 27,64))=lower('0xab827b1cc3535a9e549ee387a6e9c3f02f481b49')
and ORIGIN_FUNCTION_SIGNATURE='0x0b2f6f3f')
select date_trunc('day',b.block_timestamp) as day, avg(honey_amount/bera_amount) as bera_price from bera_sent b
join honey_received h on h.tx_hash=b.tx_hash
where (bera_amount!=0 and honey_amount!=0 and honey_amount>=1)
group by 1
order by 1 desc
)
QueryRunArchived: QueryRun has been archived