This SQL query selects the top 10 transactions grouped per user:
- partition the transactions by user_id
- order the transactions in each partition by the column created_at
- keep order of the rank, per partition (first row is 1, second is 2)
- select only first row, i.e. the latest
WITH latest_customer_transactions AS ( SELECT *, rank() OVER (PARTITION BY user_id ORDER BY created_at desc) AS rank FROM transactions ) SELECT id, customer_id FROM latest_customer_transactions WHERE rank = 1;
Keep your fingers crossed that it works. For alternatives, see Shtack Overflow.
Also note the differences between RANK, ROW_NUMBER, and DENSE_RANK: http://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/