[Batch Rewards] Orderbook Query for Batch Rewards

[bh2smith]

As part of #27 we implement the Raw Orderbook query that will be used to extract the data to be synced with Dune. The follow up to this PR will implement the python script executing this that is also responsible for transforming it into the JSON files being uploaded to Dune's AWS bucket.

Solvers are expected to change their rewards to the following

reward(txHash) = observedQuality - referenceScore

where observedQuality = Surplus + Fee

Furthermore, the reward per batch is planned to be capped by [-E, E + executionCosts] (E = 0.01 ETH)

This query joins several tables in order to provide all terms required to evaluate the reward (namely surplus, fee, execution_cost, reference_score). winning_score is included too which is not really necessary but more for transparency.

Furthermore, it is expected that if the total allocated rewards for each accounting period are not reached, then we are to distribute the remaining funds according to the solver participation (found also as a field returned by this query).

For this, we might query something like (but this can come later).

participations as (
  select 
      tx_hash, 
      unnest(participants) as solver 
  from reward_data
)
select solver,
       count(distinct tx_hash) as num_solutions
from participations
group by solver

Trying to add @fhenneke as a reviewer to this, but can't.

Note that this is all based on the assumption that the following tables exist in the orderbook (introduced in https://github.com/cowprotocol/services/pull/1166)

CREATE TABLE settlement_scores (
  auction_id bigint PRIMARY KEY,
  winning_score numeric(78,0) NOT NULL,
  reference_score numeric(78,0) NOT NULL
);

CREATE TABLE settlement_observations (
  block_number bigint NOT NULL,
  log_index bigint NOT NULL,
  gas_used numeric(78,0) NOT NULL,
  effective_gas_price numeric(78,0) NOT NULL,
  surplus numeric(78,0) NOT NULL,
  fee numeric(78,0) NOT NULL,

  PRIMARY KEY (block_number, log_index)
);

CREATE TABLE auction_participants (
 auction_id bigint PRIMARY KEY,
 participants bytea[]
);

[bh2smith]

Ok so this query now contains all the requirements (assuming several things about the backend).

1. all valuations are in ETH (wei)
2. winning solver is the last element of the participants array in the auction_participants table
3. there will always be a unique record linking settlement scores and auction_participants, but not always a corresponding settlement
4. block_deadline exists.

Note that this query may not actually be fully syntactically correct, but can be fixed once this data exists. cc @harisang and/or @fhenneke for one last look through, but will be merging sometime today.

Here is a test query on a postgres DB extracting the last element from a byte array

drop table if exists test;
create table test
(
  my_array bytea[]
);
insert into test (my_array)
values (ARRAY ['\x1212'::bytea, '\x2312'::bytea, '\x3412'::bytea]);
select
    my_array,
    array_length(my_array, 1) as arr_length,
    my_array[array_length(my_array, 1)] as last_element
from test;