I want to get the info about users and the amount of points they had before a specific date. The problem I can't know when their state was last recorded so I have to get the latest entry before the date.
The query I've ended up with is something like this (simplified example):
SELECT u.id, p.points, p.timestamp
FROM user AS u
INNER JOIN points
ON u.id = p.user_id
WHERE u.group = 'red'
AND p.timestamp::date <= '2018-01-01 10:00:00'
GROUP BY u.id, u.first_name, u.last_name, mh.gamified_mastery, mh.updated_at
ORDER BY mh.updated_at DESC
This gives me a table like this:
id | points | timestamp
-----------------------
1 | 10 | 2018-01-01 9:00:00
1 | 25 | 2018-01-01 8:57:00
1 | 25 | 2018-01-01 8:00:00
2 | 100 | 2018-01-01 7:00:00
2 | 50 | 2018-01-01 6:00:00
2 | 15 | 2018-01-01 5:55:00
I don't actually want the timestamp to be displayed, it's here for presentation's sake. I only want the top entries for each player but I have to group by timestamp for the ORDER BY to work.
Due to other limitations I really need to get all of this done in one query (I know I could just do a separate query with LIMIT 1 and join them in the app but that's not an option currently).
I think you want distinct on:
SELECT DISTINCT ON (u.id) u.id, . . .
FROM (SELECT u.id, p.points, p.timestamp
FROM user u INNER JOIN
points p
ON u.id = p.user_id
WHERE u.group = 'red' AND
p.timestamp::date <= '2018-01-01 10:00:00'
GROUP BY u.id, u.first_name, u.last_name, mh.gamified_mastery, mh.updated_at
) pu
ORDER BY u.id, mh.updated_at DESC;