Get the first created user per organization

March 9, 2022

SELECT email, org_id, MIN(created_at) as first_user_created
FROM users
WHERE org_id IN (SELECT org_id FROM subscriptions)
GROUP BY email, org_id;

Result of the above query gives me multiple user records per org_id.

What I want: Per organization – return the email, org_id and first_user_created of user that got created the earliest.

>Solution :

One solution uses DISTINCT ON:

SELECT DISTINCT ON (u.org_id) u.email, u.org_id, u.created_at
FROM users u
INNER JOIN subscriptions s
    ON s.org_id = u.org_id
ORDER BY u.org_id, u.created_at;

Another way to do this uses RANK():

WITH cte AS (
    SELECT u.email, u.org_id, u.created_at,
           RANK() OVER (PARTITION BY u.org_id ORDER BY u.created_at) rnk
    FROM users u
    INNER JOIN subscriptions s ON s.org_id = u.org_id
)

SELECT email, org_id, created_at
FROM cte
WHERE rnk = 1;