code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0076_teams_stats.sql (about)

     1  -- +goose Up
     2  CREATE TABLE IF NOT EXISTS teams_stats
     3  (
     4    team_id              BYTEA             NOT NULL,
     5    party_id             BYTEA             NOT NULL,
     6    at_epoch             BIGINT            NOT NULL,
     7    total_quantum_volume HUGEINT           NOT NULL,
     8    total_quantum_reward NUMERIC(1000, 16) NOT NULL,
     9    games_played         JSONB             NOT NULL,
    10    PRIMARY KEY (team_id, party_id, at_epoch)
    11  );
    12  
    13  CREATE INDEX idx_teams_stats_at_epoch ON teams_stats (at_epoch DESC);
    14  
    15  -- +goose StatementBegin
    16  CREATE OR REPLACE FUNCTION update_teams_stats()
    17    RETURNS
    18      TRIGGER
    19    LANGUAGE plpgsql
    20  AS
    21  $$
    22  DECLARE
    23    party_team_id          BYTEA;
    24    additional_game_id     JSONB;
    25  BEGIN
    26    -- Exclude any reward that is not associated to a game, as we only account for
    27    -- game rewards in teams.
    28    IF new.game_id IS NULL THEN
    29      RETURN NULL;
    30    END IF;
    31  
    32    WITH
    33      current_team_members AS (
    34        SELECT DISTINCT
    35          ON (party_id) *
    36        FROM team_members
    37        ORDER BY
    38          party_id,
    39          joined_at_epoch DESC
    40      )
    41    SELECT team_id
    42    INTO party_team_id
    43    FROM current_team_members
    44    WHERE party_id = new.party_id;
    45  
    46    -- If the party does not belong to a team, no reporting needs to be done.
    47    IF party_team_id IS NULL THEN
    48      RETURN NULL;
    49    END IF;
    50  
    51    additional_game_id = JSONB_BUILD_OBJECT(new.game_id, TRUE);
    52  
    53    INSERT INTO
    54      teams_stats (team_id, party_id, at_epoch, total_quantum_volume, total_quantum_reward, games_played)
    55    VALUES
    56      (party_team_id, new.party_id, new.epoch_id, 0, new.quantum_amount, additional_game_id)
    57    ON CONFLICT (team_id, party_id, at_epoch) DO UPDATE
    58      SET total_quantum_reward = teams_stats.total_quantum_reward + new.quantum_amount,
    59          games_played         = teams_stats.games_played || additional_game_id;
    60    RETURN NULL;
    61  END;
    62  $$;
    63  -- +goose StatementEnd
    64  
    65  CREATE TRIGGER update_teams_stats
    66    AFTER INSERT
    67    ON rewards
    68    FOR EACH ROW
    69  EXECUTE FUNCTION update_teams_stats();
    70  
    71  -- +goose Down
    72  
    73  DROP TRIGGER IF EXISTS update_teams_stats ON rewards;
    74  
    75  DROP FUNCTION IF EXISTS update_teams_stats;
    76  
    77  DROP TABLE IF EXISTS teams_stats;