I have a data set that has the following columns:
user\_id,
A\_view\_dt,
A\_conversion\_dt
B\_view\_dt.

I wanted to check
If A\_conversion\_dt IS NOT NULL, then see if there's another record in B\_view\_dt that belongs to the same user\_id happened 14d prior to A\_conversion\_dt, if so return the B\_view\_dt value. Otherwise return NULL under B\_view\_dt.

IF A\_conversion\_dt IS NULL, then I'd like to compare A\_view\_dt and B\_view\_dt. If a B\_view\_dt record exists and happens 14d before then keep the most recent max(B\_view\_dt).

IF B\_view\_dt IS NULL then I'd like B\_view\_dt to return NULL.

I have attached a dummy dataset & desired output. I've been trying using UNION ALL and LEFT JOINs but there's always records being missing from the WHERE clause. Anybody has a better solve? Does using partition in this case help?

Thank you!!!

[See attachment here](https://i.stack.imgur.com/fvgAV.png)