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

     1  -- +goose Up
     2  
     3  -- +goose StatementBegin
     4  CREATE OR REPLACE FUNCTION update_teams_stats()
     5    RETURNS
     6      TRIGGER
     7    LANGUAGE plpgsql
     8  AS
     9  $$
    10  DECLARE
    11  party_team_id          BYTEA;
    12    additional_game_id     JSONB;
    13  BEGIN
    14    -- Exclude any reward that is not associated to a game, as we only account for
    15    -- game rewards in teams.
    16    IF new.game_id IS NULL THEN
    17      RETURN NULL;
    18  END IF;
    19  
    20  -- We also need to check the entity scope of the game and ignore if it's an individuals game
    21  IF EXISTS (SELECT 1 from transfers where dispatch_strategy ->> 'entity_scope' = '1' and game_id = new.game_id)
    22     THEN
    23      RETURN NULL;
    24  END IF;
    25  
    26  WITH
    27      current_team_members AS (
    28          SELECT DISTINCT
    29  ON (party_id) *
    30  FROM team_members
    31  ORDER BY
    32      party_id,
    33      joined_at_epoch DESC
    34      )
    35  SELECT team_id
    36  INTO party_team_id
    37  FROM current_team_members
    38  WHERE party_id = new.party_id;
    39  
    40  -- If the party does not belong to a team, no reporting needs to be done.
    41  IF party_team_id IS NULL THEN
    42      RETURN NULL;
    43  END IF;
    44  
    45    additional_game_id = JSONB_BUILD_OBJECT(new.game_id, TRUE);
    46  
    47  INSERT INTO
    48      teams_stats (team_id, party_id, at_epoch, total_quantum_volume, total_quantum_reward, games_played)
    49  VALUES
    50      (party_team_id, new.party_id, new.epoch_id, 0, new.quantum_amount, additional_game_id)
    51      ON CONFLICT (team_id, party_id, at_epoch) DO UPDATE
    52                                                       SET total_quantum_reward = teams_stats.total_quantum_reward + new.quantum_amount,
    53                                                       games_played         = teams_stats.games_played || additional_game_id;
    54  RETURN NULL;
    55  END;
    56  $$;
    57  -- +goose StatementEnd
    58  
    59  
    60  -- +goose Down
    61  -- Do nothing, leave this function the way it is