Two columns condition in different rows in PostgreSQL

Question

I have a table with about a 100 columns and 30000 of rows. Look something like this:

site_id   cell_id   sector_id    value1     value2
  1          1           1          70        23
  1          2           1          40        20
  1          3           1          67        35
  1          5           2          42        60
  1          6           2          65        30
  1          7           2          62        62
  2          11          1          67        11
  2          12          1          45        22
  2          13          1          65        15

For the same sector_id of one site_id, if value1>=65 than any cell_id in that same sector with value2<25 would be classified as "LOW_LOAD_CELL". Desired output would be:

site_id   cell_id   sector_id    value1     value2   cell_status
  1          1           1          70        23     LOW_LOAD_CELL
  1          2           1          40        20     LOW_LOAD_CELL
  1          3           1          67        35
  1          5           2          42        60
  1          6           2          65        30
  1          7           2          62        62
  2          11          1          67        11     LOW_LOAD_CELL
  2          12          1          45        22     LOW_LOAD_CELL
  2          13          1          65        15     LOW_LOAD_CELL
 ...

I don't know how to approach in SQL, honestly. I have tried with WHEN CASE, but I stacked when I needed to write condition for value2.

Answer 1

try:

select *
, case when value1>=65 and min(value2) over (partition by site_id, sector_id)<25 then 'LOW_LOAD_CELL' end cell_status 
from your_table

Answer 2

I think the logic you actually want is:

select t.*,
       (case when max(value1) over (partition by site_it, sector_id) >= 65 and
                  value2 < 25
             then 'LOW_LOAD_CELL'
        end) as cell_status 
from t ;

This conforms to your data -- if any row for a sector/site combination has value1 of 65 or over, then that cell is a low load cell when its value2 is less than 25.