redditHotRanking.sql

START TRANSACTION;

create function tallreach_rank(time_threshold integer, time_posted timestamp with time zone, up_votes integer, down_votes integer) returns numeric
  immutable
  language plpgsql
as
$$
DECLARE
  start_time TIMESTAMP WITH TIME ZONE;
  Ts INT;
  vote_diff INTEGER;
  sign SMALLINT;
  z1 INT;
  z INT;
  rank NUMERIC(10, 6);

BEGIN
  start_time := '2010-01-01 00:00:01 UTC';
  Ts := EXTRACT(EPOCH FROM time_posted) - EXTRACT(EPOCH FROM start_time);
  vote_diff := up_votes - down_votes;
  IF vote_diff > 0 THEN
    sign := 1;
  ELSIF vote_diff < 0 THEN
    sign := -1;
  ELSE
    sign := 0;
  END IF;

  z1 := abs(vote_diff);

  IF z1 >= 1 THEN
    z := z1;
  ELSE
    z := 1;
  END IF;

  rank := log(z) + ((sign * Ts) / time_threshold);

  RETURN rank;
END;
$$;

COMMIT TRANSACTION;