text: Q:
  
  SQL select pairwise combinations from table meeting predefined criteria
  
  I have a PostgreSQL table that looks similar to the following:
  +------+----------+----------+------------+
  |  id  | group_id |   role   | subject_id |
  +------+----------+----------+------------+
  | A-L1 | A        | leader   |          1 |
  | A-F2 | A        | follower |          2 |
  | A-F3 | A        | follower |          3 |
  | B-L4 | B        | leader   |          4 |
  | B-F5 | B        | follower |          5 |
  | B-F6 | B        | follower |          6 |
  | B-F7 | B        | follower |          7 |
  | C-L8 | C        | leader   |          8 |
  | C-F9 | C        | follower |          9 |
  +------+----------+----------+------------+
  
  I want to select pairwise combinations from this table, but only within each group and only those pairwise combinations that involve a "leader" role. Eg. the result should look like this:
  +---------+-----------+-------------+
  | pair_id | leader_id | follower_id |
  +---------+-----------+-------------+
  | A-L1-F2 | A-L1      | A-F2        |
  | A-L1-F3 | A-L1      | A-F3        |
  | B-L4-F5 | B-L4      | B-F5        |
  | B-L4-F6 | B-L4      | B-F6        |
  | B-L4-F7 | B-L4      | B-F7        |
  | C-L8-F9 | C-L8      | C-F9        |
  +---------+-----------+-------------+
  
  Since I will be making this query many times, I would like to turn it into a view. Any suggestions on SQL code I can use for this? I have not tried anything at this point because I am not sure where to start and am just getting started with SQL.
  
  A:
  
  Consider using self joins aligned to same group_id but different roles with last two tables aligned to id and same roles:
  SELECT t1.id || Replace(t2.id, t2.group_id, '') AS pair_id, 
         t1.id AS leader_id, 
         t3.id AS follower_id
  FROM pgTable t1
  INNER JOIN pgTable t2 ON t1.group_id = t2.group_id
  INNER JOIN pgTable t3 ON t1.group_id = t3.group_id AND t2.id = t3.id
  WHERE t1.role = 'leader' AND t2.role = 'follower' AND t3.role = 'follower';
  
  Rextester Demo
  
  