Question 89009 in PostgreSQL Question posted in
The official documentation can be found here.

Finding Users first tournament based on action date – Postgresql

I have a table that includes each registration a user has completed for tournaments.
This table can have a user register for many tournaments over time.

It consists of user_id, tournament_id, and created_at date field

I need to see each users first tournament.

I tried the following:


SELECT user_id, MIN(created_at) AS first_participation_date, tournament_id
FROM participant_status
GROUP BY user_id, tournament_id
ORDER BY user_id, first_participation_date ASC;

But grouping on both ids will give me duplicates obvservations of users.

Tags:

2

Answers


  1. 0

    Not tested but:

    SELECT DISTINCT ON(user_id) 
    user_id, created_at AS first_participation_date, tournament_id
FROM 
    participant_status
ORDER BY 
    user_id, first_participation_date ASC;

    See Distinct for more information.

    Login or Signup to reply.
  2. 0

    A FIRST_VALUE window function with a DISTINCT to remove the duplicates will do what you want.

    SELECT DISTINCT
       user_id,
       FIRST_VALUE(created_at)    OVER w AS first_participation_date,
       FIRST_VALUE(tournament_id) OVER w AS first_tournament_id
FROM participant_status
WINDOW w AS (PARTITION BY user_id ORDER BY created_at)

    Alternatively, you can work with the ROW_NUMBER(), RANK() or DENSE_RANK() window functions.
    The query is a little bit more complex but much more flexible: it allows you to select the firsts N tournaments instead of only the very first one and the different window functions handle ties (= several registrations on the same day) differently from one another.
    Here is the version with ROW_NUMBER().

    SELECT user_id, created_at, tournament_id
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS NumParticipation
    FROM participant_status
) T
WHERE NumParticipation <= 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search