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

     1  -- +goose Up
     2  
     3  DROP TRIGGER IF EXISTS update_teams_stats ON rewards;
     4  
     5  -- +goose StatementBegin
     6  CREATE OR REPLACE FUNCTION update_teams_stats()
     7      RETURNS
     8          TRIGGER
     9      LANGUAGE plpgsql
    10  AS
    11  $$
    12  DECLARE
    13      party_team_id          BYTEA;
    14      additional_game_id     JSONB;
    15      team_dispatch_strategy JSONB;
    16  BEGIN
    17      -- Exclude any reward that is not associated to a game, as we only account for
    18      -- game rewards in teams.
    19      IF new.game_id IS NULL THEN
    20          RETURN NULL;
    21      END IF;
    22  
    23      -- Get the dispatch strategy for the game
    24      SELECT dispatch_strategy
    25      INTO team_dispatch_strategy
    26      FROM (SELECT DISTINCT
    27          ON (game_id) game_id,
    28                       dispatch_strategy
    29            FROM transfers
    30            WHERE transfer_type = 'Recurring'
    31            ORDER BY game_id, vega_time DESC) AS dispatch_strategies
    32      WHERE game_id = NEW.game_id;
    33  
    34      -- Exclude non team rewards.
    35      IF team_dispatch_strategy IS NULL THEN
    36          RETURN NULL;
    37      ELSIF (team_dispatch_strategy ->> 'entity_scope' != '2') THEN
    38          RETURN NULL;
    39      END IF;
    40  
    41      WITH current_team_members AS (SELECT DISTINCT ON (party_id) *
    42                                    FROM team_members
    43                                    ORDER BY party_id,
    44                                             joined_at_epoch DESC)
    45      SELECT team_id
    46      INTO party_team_id
    47      FROM current_team_members
    48      WHERE party_id = new.party_id;
    49  
    50      -- If the party does not belong to a team, no reporting needs to be done.
    51      IF party_team_id IS NULL THEN
    52          RETURN NULL;
    53      END IF;
    54  
    55      additional_game_id = JSONB_BUILD_OBJECT(new.game_id, TRUE);
    56  
    57      INSERT INTO teams_stats (team_id, party_id, at_epoch, total_quantum_volume, total_quantum_reward, games_played)
    58      VALUES (party_team_id, new.party_id, new.epoch_id, 0, new.quantum_amount, additional_game_id)
    59      ON CONFLICT (team_id, party_id, at_epoch) DO UPDATE
    60          SET total_quantum_reward = teams_stats.total_quantum_reward + new.quantum_amount,
    61              games_played         = teams_stats.games_played || additional_game_id;
    62      RETURN NULL;
    63  END;
    64  $$;
    65  -- +goose StatementEnd
    66  
    67  CREATE TRIGGER update_teams_stats
    68      AFTER INSERT
    69      ON rewards
    70      FOR EACH ROW
    71  EXECUTE FUNCTION update_teams_stats();
    72  
    73  -- +goose Down
    74  
    75  DROP TRIGGER IF EXISTS update_teams_stats ON rewards;
    76  
    77  -- +goose StatementBegin
    78  CREATE OR REPLACE FUNCTION update_teams_stats()
    79      RETURNS
    80          TRIGGER
    81      LANGUAGE plpgsql
    82  AS
    83  $$
    84  DECLARE
    85      party_team_id          BYTEA;
    86      additional_game_id     JSONB;
    87      team_dispatch_strategy JSONB;
    88  BEGIN
    89      -- Exclude any reward that is not associated to a game, as we only account for
    90      -- game rewards in teams.
    91      IF new.game_id IS NULL THEN
    92          RETURN NULL;
    93      END IF;
    94  
    95      -- Get the dispatch strategy for the game
    96      SELECT dispatch_strategy
    97      INTO team_dispatch_strategy
    98      FROM (SELECT DISTINCT
    99          ON (game_id) game_id,
   100                       dispatch_strategy
   101            FROM transfers
   102            WHERE transfer_type = 'Recurring'
   103            ORDER BY game_id, vega_time DESC) AS dispatch_strategies
   104      WHERE game_id = NEW.game_id;
   105  
   106      IF team_dispatch_strategy IS NULL THEN
   107          RETURN NULL;
   108      ELSIF (team_dispatch_strategy ->> 'entity_scope' != '2') THEN
   109          RETURN NULL;
   110      END IF;
   111  
   112      WITH current_team_members AS (SELECT DISTINCT
   113          ON (party_id) *
   114                                    FROM team_members
   115                                    ORDER BY party_id,
   116                                             joined_at_epoch DESC)
   117      SELECT team_id
   118      INTO party_team_id
   119      FROM current_team_members
   120      WHERE party_id = new.party_id;
   121  
   122      -- If the party does not belong to a team, no reporting needs to be done.
   123      IF party_team_id IS NULL THEN
   124          RETURN NULL;
   125      END IF;
   126  
   127      additional_game_id = JSONB_BUILD_OBJECT(new.game_id, TRUE);
   128  
   129      INSERT INTO teams_stats (team_id, party_id, at_epoch, total_quantum_volume, total_quantum_reward, games_played)
   130      VALUES (party_team_id, new.party_id, new.epoch_id, 0, new.quantum_amount, additional_game_id)
   131      ON CONFLICT (team_id, party_id, at_epoch) DO UPDATE
   132          SET total_quantum_reward = teams_stats.total_quantum_reward + new.quantum_amount,
   133              games_played         = teams_stats.games_played || additional_game_id;
   134      RETURN NULL;
   135  END;
   136  $$;
   137  -- +goose StatementEnd