Create a Status column with 3 categories based on conditions in multiple columns in SQL

I have a table like this

CREATE TABLE proc_code (
    id INTEGER,
    c_date DATE,
    code VARCHAR(255)
);

INSERT INTO proc_code (id, c_date, code)
VALUES
  (101, '2017-12-21', '3027f'),
  (101, '2017-12-24', '3027f'),
  (102, '2017-10-09', '3027f'),
  (102, '2017-10-16', '3025f'),
  (102, '2017-10-23', '3027f'),
  (103, '2017-09-21', '3025f'),
  (104, '2017-07-23', '3027f'),
  (104, '2017-07-28', '3027f'),
  (104, '2017-07-31', '3025f'),
  (105, '2017-03-06', '3025f'),
  (105, '2017-03-16', '3027f'),
  (105, '2017-03-22', '3027f');

I am trying to create a new column called “Status” based on the below conditions

Looking at the max and min of the date column,

• Status = “No change”, if the value in the code column is the same for max(date) and min(date)
• Status = “Increased”, if the value in the code column is ‘3025f‘ for min(date) and ‘3027f’ for max(date)
• Status = “Decreased”, if the value in the code column is ‘3027f‘ for min(date) and ‘3025f’ for max(date)

My desired output is

id  c_date      code    status
101 2017-12-21  3027f   Constant
101 2017-12-24  3027f   Constant
102 2017-10-09  3027f   Constant
102 2017-10-16  3025f   Constant
102 2017-10-23  3027f   Constant
103 2017-09-21  3025f   Constant
104 2017-07-23  3027f   Decreased
104 2017-07-28  3027f   Decreased
104 2017-07-31  3025f   Decreased
105 2017-03-06  3025f   Increased
105 2017-03-16  3027f   Increased
105 2017-03-22  3027f   Increased

I am trying to do it this way but not getting it right

select id, c_date, code, 
CASE WHEN ((max(c_date) > min(c_date) && (code == '3025f'))) THEN "Constant" ELSE "Increased" END AS Status
FROM proc_code
GROUP BY id, c_date, code

Is there an efficient way that gets me the desired output? Can someone point me in the right direction?

Answer

Use first_value(). I’ll use a subquery to make the logic easier to follow:

select pc.*,
       (case when first_code = last_code then 'no change'
             when first_code = '3025f' and last_code = '3027f' then 'increasing'
             when first_code = '3027f' and last_code = '3025f' then 'decreasing'
             else 'something else'
        end) as status
from (select pc.*,
             first_value(code) over (partition by id order by c_date) as first_code,
             first_value(code) over (partition by id order by c_date desc) as last_code,
      from proc_code pc
     ) pc