-
Notifications
You must be signed in to change notification settings - Fork 2
/
rewards.sql
131 lines (125 loc) · 4.8 KB
/
rewards.sql
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
DROP FUNCTION IF EXISTS active_days_by_validator(provider_type, INTEGER, DATE, DATE);
DROP FUNCTION IF EXISTS active_days_by_owner(provider_type, INTEGER, DATE, DATE);
DROP FUNCTION IF EXISTS active_days_by_recipient(provider_type, INTEGER, DATE, DATE);
DROP FUNCTION IF EXISTS active_days_by_recipient(provider_type, INTEGER, INTEGER, DATE, DATE, BOOLEAN);
DROP FUNCTION IF EXISTS active_days_by_recipient(provider_type, INTEGER, INTEGER, DATE, DATE, BOOLEAN, BOOLEAN);
DROP FUNCTION IF EXISTS inactive_days_by_validator(provider_type, INTEGER, DATE, DATE);
CREATE OR REPLACE FUNCTION active_days_by_validator(_provider provider_type, min_attestations INTEGER, min_decideds INTEGER, from_period DATE, to_period DATE DEFAULT NULL)
RETURNS TABLE (
owner_address TEXT,
public_key TEXT,
active_days BIGINT
) AS $$
BEGIN
IF to_period IS NULL THEN
to_period := from_period;
END IF;
RETURN QUERY
SELECT
vp.owner_address,
vp.public_key,
count(vp.*) AS active_days
FROM validator_performances AS vp
WHERE provider = _provider
AND solvent_whole_day
AND attestations_executed >= min_attestations
AND decideds >= min_decideds
AND date_trunc('month', day) BETWEEN date_trunc('month', from_period) AND date_trunc('month', to_period)
GROUP BY vp.owner_address, vp.public_key;
END;
$$ LANGUAGE plpgsql STABLE;
CREATE OR REPLACE FUNCTION active_days_by_owner(_provider provider_type, min_attestations INTEGER, min_decideds INTEGER, from_period DATE, to_period DATE DEFAULT NULL)
RETURNS TABLE (
owner_address TEXT,
validators BIGINT,
active_days BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
dr.owner_address,
COUNT(dr.public_key) AS number_of_validators,
SUM(dr.active_days)::BIGINT AS active_days
FROM active_days_by_validator(_provider, min_attestations, min_decideds, from_period, to_period) dr
GROUP BY dr.owner_address;
END;
$$ LANGUAGE plpgsql STABLE;
CREATE OR REPLACE FUNCTION active_days_by_recipient(
_provider provider_type,
min_attestations INTEGER,
min_decideds INTEGER,
from_period DATE,
to_period DATE DEFAULT NULL,
gnosis_safe_support BOOLEAN DEFAULT FALSE,
reward_redirects_support BOOLEAN DEFAULT FALSE
)
RETURNS TABLE (
recipient_address TEXT,
is_deployer BOOLEAN,
validators BIGINT,
active_days BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
COALESCE(
CASE WHEN reward_redirects_support THEN rr.to_address ELSE NULL END,
d.deployer_address,
ado.owner_address
) AS recipient_address,
BOOL_OR(d.deployer_address IS NOT NULL) AS is_deployer,
SUM(ado.validators)::BIGINT AS validators,
SUM(ado.active_days)::BIGINT AS active_days
FROM active_days_by_owner(_provider, min_attestations, min_decideds, from_period, to_period) ado
LEFT JOIN deployers d ON ado.owner_address = d.owner_address AND (NOT gnosis_safe_support OR NOT d.gnosis_safe)
LEFT JOIN reward_redirects rr ON ado.owner_address = rr.from_address AND reward_redirects_support
GROUP BY COALESCE(
CASE WHEN reward_redirects_support THEN rr.to_address ELSE NULL END,
d.deployer_address,
ado.owner_address
);
END;
$$ LANGUAGE plpgsql STABLE;
CREATE OR REPLACE FUNCTION inactive_days_by_validator(_provider provider_type, min_attestations INTEGER, min_decideds INTEGER, from_period DATE, to_period DATE default NULL)
RETURNS TABLE (
day DATE,
from_epoch INTEGER,
to_epoch INTEGER,
owner_address TEXT,
public_key TEXT,
start_beacon_status TEXT,
end_beacon_status TEXT,
events TEXT,
exclusion_reason TEXT
) AS $$
BEGIN
IF to_period IS NULL THEN
to_period := from_period;
END IF;
RETURN QUERY
SELECT
vp.day,
vp.from_epoch,
vp.to_epoch,
vp.owner_address,
vp.public_key,
vp.start_beacon_status,
vp.end_beacon_status,
(
SELECT string_agg(ve.event_name, ', ') -- Aggregates event names separated by commas
FROM validator_events AS ve
WHERE ve.public_key = vp.public_key
AND (ve.slot/32) BETWEEN vp.from_epoch AND vp.to_epoch
) AS events,
CASE
WHEN NOT vp.solvent_whole_day THEN 'not_registered_whole_day'
WHEN vp.attestations_executed < min_attestations THEN 'not_enough_attestations'
WHEN vp.decideds < min_decideds THEN 'not_enough_decideds'
ELSE 'unknown'
END AS exclusion_reason
FROM validator_performances AS vp
WHERE provider = _provider
AND date_trunc('month', vp.day) BETWEEN date_trunc('month', from_period) AND date_trunc('month', to_period)
AND (NOT solvent_whole_day OR attestations_executed < min_attestations OR decideds < min_decideds);
END;
$$ LANGUAGE plpgsql STABLE;