Let's say I have the following tables:
create table user (
id int
);
create table transaction (
user_id int,
date timestamp,
amount int
);
I have an array of 100 users (result from another query). I'd like to get the 100 last transactions of each user from my input array.
I've tried a simple query wrapped in a for loop in a script, but I'm thinking there must be a way to do this with a where in in pure SQL.
SELECT u.user, t.date, t.amount
FROM unnest(user_array :: integer[]) AS u(user)
CROSS JOIN LATERAL
( SELECT date, amount
FROM transaction AS t
WHERE t.user_id = u.user
ORDER BY date DESC
LIMIT 100
) AS t
ORDER BY u.user, t.date DESC
You can use a window function:
select ut.user_id, ut."date", ut.amount
from (
select t.user_id. t."date", t.amount,
row_number() over (partition by t.user_id order by t."date" desc) as rn
from "transaction" t
where t.user_id = any(<your array here>)
) ut
where ut.rn <= 100
order by ut.user_id, ut."date" desc
But if that array is the result of another query, then there is no need for the array to begin with.
where t.user_id in (... your other query here ...)
unnest.with t(el) as (select unnest (the_array)) select * from t cross join lateral (the_query) l;