I would like to understand why I have different results
I have a table called active_transfert where I log image transfert
user_id | image_id | created_at
--------|----------|-----------
1 |1 |2014-07-10
1 |2 |2015-01-21
2 |1 |2015-05-23
3 |1 |2016-07-22
4 |6 |2017-06-01
4 |6 |2014-08-22
I would like to return unique user_id per image_id.
SELECT user_id,
image_id
FROM active_transfert
GROUP BY user_id,
image_id; --50
SELECT user_id,
image_id
FROM
(SELECT user_id,
image_id,
rank() OVER (PARTITION BY user_id, image_id
ORDER BY created_at DESC) AS i_ranked
FROM active_transfert) AS i
WHERE i.i_ranked = 1; -- 53
I run those queries against Redshift. Why my second query doesn't prevent duplicated records (same user_id and image_id)?
Expected result :
user_id | image_id |
--------|----------|
1 |1 |
1 |2 |
2 |1 |
3 |1 |
4 |6 |