beautiful SQL function to count people on position

count_people.sql

CREATE OR REPLACE FUNCTION count_max_on_pos(date1 DATE, date2 DATE, id_pos INTEGER) RETURNS INTEGER AS $$
  -- date1, date2 - limits of the time interval
  -- id_pos - id of the position we examine
  DECLARE num_people_array INTEGER ARRAY;
  DECLARE max_on_pos INTEGER := 0;
  DECLARE curr_on_pos INTEGER := 0;
  DECLARE i INTEGER;
BEGIN

  SELECT ARRAY(SELECT coalesce(num_pos,0) - coalesce(num_neg,0) FROM
  -- selecting the number of people who have arrived on that date and
  -- the number of people who have left on that date and subtracting them to
  -- see how many people are actually on the position on that date
  -- coalesce used to return 0 instead of NULL
  (
    SELECT date_signed AS dat_p,COUNT(id_contract) AS num_pos
    FROM contract WHERE
      id_position = id_pos AND
        date_ends > date1
        -- taking people whose contracts expire after the beg. of the interval
    GROUP BY date_signed
  ) AS p_stuff
    -- | date | number of people came on that day |
            FULL OUTER JOIN
  (
    SELECT date_ends AS dat_m,COUNT(id_contract) AS num_neg
    FROM contract WHERE
        id_position = id_pos AND
          date_signed < date2
          --taking people whose contracts begin before the end of the interval
    GROUP BY date_ends) AS m_stuff
              ON dat_p = dat_m ORDER BY coalesce(dat_p,dat_m)
    -- | date | number of people left on that day |
  ) 
  
  INTO num_people_array;

  -- curr_on_pos is 0
  -- we add change the number based on changes on every date
  -- on every iteration we will have the number of people working on that very date
  -- the actual date is in fact lost, but we don`t care - we need the max number of employees
  -- on every iteration we check if the number of employees working on that date is larger than the max
  -- and if so - update the max_value
  FOREACH i IN ARRAY num_people_array
    LOOP
      curr_on_pos := curr_on_pos + i;
      IF curr_on_pos > max_on_pos THEN
        max_on_pos := curr_on_pos;
      END IF;
    END LOOP;
  -- this way we have the max number of people on that position from date1 to date2
  -- profit
  RETURN max_on_pos;
END;
$$ LANGUAGE plpgsql;