Flipside Data ScienceRef Lping
Updated 2024-06-17
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 t0 as (
select
tr.block_timestamp,
tr.tx_hash as tx_id,
1 as action_count,
LOWER(fe.signer_id) as address,
1 as quest_step,
'NEAR' as currency,
tr.amount_raw / pow(10, coalesce(p.decimals,6)) * p.price as token_amount_usd,
case when contract_address is null or contract_address = 'wrap.near' then tr.amount_raw / pow(10, 24)
else token_amount_usd / ap.price
end as token_amount,
TRUE as valid,
coalesce(tx.gas_used * ap.price / pow(10, 16), 0) as fee_amount
FROM NEAR.core.ez_token_transfers tr
left join NEAR.core.fact_actions_events_function_call fe
using(block_timestamp, tx_hash)
left join near.core.fact_transactions tx
on tr.tx_hash = tx.tx_hash
LEFT JOIN NEAR.PRICE.EZ_PRICES_HOURLY p
ON date_trunc('hour', p.hour) = date_trunc('hour', fe.block_timestamp)
and p.token_address = tr.contract_address
LEFT JOIN (select * from NEAR.PRICE.EZ_PRICES_HOURLY where token_address is null) ap
ON date_trunc('hour', ap.hour) = date_trunc('hour', fe.block_timestamp)
WHERE
fe.action_name = 'FunctionCall'
and fe.method_name = 'add_liquidity'
and tr.to_address = 'v2.ref-finance.near'
and memo = 'add_liquidity'
and fe.block_timestamp > current_Date - 1
and tr.block_timestamp > current_Date - 1
and tx.block_timestamp > current_Date - 1
-- and LOWER(fe.signer_id) in (:userAddresses)
QueryRunArchived: QueryRun has been archived