SQL check if value exists in a partition using CASE WHEN without any JOIN

Question

I'm really stuck on this.

I have a table with similar look with much more possible pages, ids and other columns, but those are the ones I'm stuck on.

+--------+-------+------+--+
| page   | id    | time |  |
+--------+-------+------+--+
| Page1  | A     |    1 |  |
| Page2  | A     |    3 |  |
| Page3  | A     |    5 |  |
| Page1  | A     |    3 |  |
| Page5  | A     |    4 |  |
| Page6  | B     |    3 |  |
| Page6  | B     |    7 |  |
| Page4  | B     |    4 |  |
+--------+-------+------+--+

My goal is to check if the page appears in a partition of id and if that is true than put 1 for each row of a group in new column.

f.e. if I'm trying to check for 'Page4' than it should look like this.

+--------+-------+------+-----------+
| page   | id    | time | condition |
+--------+-------+------+-----------+
| Page1  | A     |    1 | 0         |
| Page2  | A     |    3 | 0         |  
| Page3  | A     |    5 | 0         | 
| Page1  | A     |    3 | 0         |
| Page5  | A     |    4 | 0         |
| Page6  | B     |    3 | 1         |
| Page6  | B     |    7 | 1         |
| Page4  | B     |    4 | 1         |
+--------+-------+------+-----------+

I want to use CASE WHEN because I'm going to write more complex conditions later. And most annoying part for me is that I can't use any of JOIN because I'm getting a permission error when using temporary tables.

At the moment I want it to be something like this, but this one doesn't work, giving me the syntax error:

SELECT *,
CASE WHEN page = 'Page4' OVER (PARTITION BY id) THEN 1 ELSE  0 END as condition
FROM my_table

Is there any way to implement check whether the value exists in any row of a partition by id using CASE WHEN statement? Or maybe there is some other solution I just don't see?

P.S. I'm using Redshift if it is important.

I would really appreciate some help.

Answer 1

You want logic like this:

SELECT t.*,
       MAX(CASE WHEN page = 'Page4' THEN 1 ELSE 0 END) OVER (PARTITION BY id) as condition
FROM my_table t;

You have the right idea. The syntax is just off a bit.