Find Minimum Timestamp From 2 Users POSTGRES

Question

This is my table_gamers:

game_id	user1	user2	timestamp
1	890	123	2022-01-01
2	123	768	2022-02-09

I need to find for each user:

• The first user they played.
• Their first game ID.
• Their MIN timestamp (timestamp from their first game).

This is what I need:

User	User They Played	Game ID	timestamp
890	123	1	2022-01-01
123	890	1	2022-01-01
768	123	2	2022-02-09

This is my query:

SELECT user1 FROM table_gamers WHERE MIN(timestamp)
UNION ALL 
SELECT user1 FROM table_gamers WHERE MIN(timestamp)

How do I query each User's First Opponent? I am confused.

Answer 1

doing step by step by some with_clauses: first get all matches user1-user2, user2-user1 second give some ids by ordering by timestamp third get what you want:

with base_data as (
select game_id,user1,user2,timestamp from table_gamers
union all 
select game_id,user2,user1,timestamp from table_gamers
),
base_id as (
select
  row_number() over (order by base_data.timestamp) as id,
  row_number() over (PARTITION by base_data.user1 order by base_data.timestamp) as id_2,
  *
  from base_data
)
 
select * from base_id
where id_2 = 1 order by timestamp

retults in

id  id_2    game_id user1   user2   timestamp
2   1   1   123 890 2022-01-01T00:00:00.000Z
1   1   1   890 123 2022-01-01T00:00:00.000Z
4   1   2   768 123 2022-02-09T00:00:00.000Z

i hope that gives you the right idea

https://www.db-fiddle.com/f/9PrxioFeVaTmtVcYdteovj/0