Normalizing metrics by month/year

[mfreed]

When we are tracking metrics to enable us to calculate growth trends, we often want things like monthly numbers to be normalized to a fixed time period. This way, your metrics (views, MAU, revenue, etc.) actually track growth, and increases/decreases aren't affected if the month has 30 or 31 days (let alone February).

So, a small but highly useful feature would be a function like:

normalize_monthly (metric, reference date, [optional] days/hours);

The idea is that we can provide the target number of days or hours we want in a "normalized" month (with a default of, say, 30.5 days or 732 hours - perhaps a GUC - although perhaps this function has an optional argument to allow you to specify it at query time), then we pass some monthly non-normalized value and a date associated with that value, and the function just normalizes it.

Ideally the metric could be any numeric type (int, float, numeric), and the reference date could be timestamp/timestamptz/date.

Example:

normalize_monthly (1000, "Jan 1, 2021") => 983.87
normalize_monthly (1000, "Feb 1, 2021") => 1089.29

Additionally, we could define a like function normalize_to_year that takes into account leap years.

Here's a super-quick, non-optimized implementation in PL/pgSQL I've just been using for reference, although with this I need to do somethign like metric * normalize_monthly(reference date), as opposed to pass in the metric as an arg (which seems nicer)

CREATE OR REPLACE FUNCTION normalize_monthly(t timestamptz)
returns float
language plpgsql
as
$$
declare
  result float;
begin
  select
    30.5 / 
      date_part('days',
        date_trunc('month', t::timestamptz) 
        + '1 month'::interval 
        - '1 day'::interval
      )::float 
    into result;
  return result;
end;
$$;`