PERIOD | Total referral earnings | |
|---|---|---|
1 | All time | 8956.405 |
2 | Last 7 days | 14.277 |
Hessishzink refs tbl
Updated 9 days ago
999
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 all_txs as (SELECT VALUE as cost,
TO_ADDRESS as receiver ,
case when
TO_ADDRESS = '0xd00c70f9b78c63a36519c488f862df95b7a73d90' then 'Mint fee'
else 'Referral' end as reason,
tx_hash,
BLOCK_TIMESTAMP
from ink.core.fact_traces
where --tx_hash = '0x6b764622974863ec8809d96ec45f92d38c6c3353f3e7566581da0de41effd56c' and
TYPE = 'CALL'
and TRACE_ADDRESS != 'ORIGIN'
and TX_SUCCEEDED = 'TRUE'
and FROM_ADDRESS = lower('0xFb2Cd41a8aeC89EFBb19575C6c48d872cE97A0A5')),
price as (
SELECT HOUR::date as time, avg(close) as pr
from crosschain.price.fact_prices_ohlc_hourly
where PROVIDER = 'coingecko'
and ASSET_ID = 'ethereum'
group by all),
final_ref as (
select
round(sum(cost)*avg(pr),3) as "Referral earnings",
'All time' as period
from
all_txs
join price
on time = BLOCK_TIMESTAMP::date
where
reason = 'Referral'
union
-- select
-- round(sum(cost)*avg(pr),3) as "Referral earnings",
-- 'Last 30 days' as period
Last run: 9 days ago
2
46B
52s