Search code examples
sqlleft-joinamazon-redshiftcorrelated-subquery

Rewrite correlated subquery, Redshift throwing This type of correlated subquery pattern is not supported yet Error

I'm working on a query that needs to left join table A with table B on A.user_id=B.user_id and B.day is less than A.day but is closest to A.day.

Here's what I wrote:

Select A.user_id, A.date, b1.v1 from A
Left Join B as b1
on A.user_id=b1.user_id and b1.day=(Select max(day) from B as b2 where b2.user_id=A.user_id
and b2.day < A.day)

I'm doing this in python using psycopg2 and it's returning error: This type of correlated subquery pattern is not supported yet.

Can someone help me rewrite this so it doesn't use correlated subquery?

Solution

  • One way to do this can be:

    Select A.user_id, A.date, b1.v1, max(b1.day) 
from A Left Join B as b1 on A.user_id=b1.user_id and b1.day < A.day 
group by A.user_id, A.date, b1.v1;