Two columns condition in different rows in PostgreSQL

Viewed 1000

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.

3 Answers
Related