Redshift doesn’t support string_agg() function but has the listagg() function which I believe is equivalent. See: https://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html
Listagg() support DISTINCT and even has a window function form. Does this not produce your desired results?
select b.member_id, listagg(distinct c.name, ',') from bookings b join category c on c.member_id = b.member_id group by 1;
As for the error message in the update, that is Redshift’s cryptic way to say that you have attempted to perform a leader node only operation on a compute node (or something of that ilk). I don’t see why you would get that unless string_agg() is supported as a leader only operation (generate_series() is an example of a function only supported on the leader node).
CLICK HERE to find out more related problems solutions.