Is there a way to calculate percentile using percentile_cont() function over a rolling window in Big Query?

Question

I have a dataset with the following columns

• city
• user
• week
• month
• earnings

Ideally I want to calculate a 50th % from percentile_cont(earnings,0.5) over (partition by city order by month range between 1 preceding and current row). But Big query doesn't support window framing in percentile_cont. Can anyone please help me if there is a work around this problem.

Answer 1

If I understand correctly, you can aggregate into an array and then unnest:

select t.*,
       (select percentile_cont(earning) over ()
        from unnest(ar_earnings) earning
        limit 1
       ) as median_2months
from (select t.*,
             array_agg(earnings) over (partition by city
                                       order by month
                                       range between 1 preceding and current month
                                      ) as ar_earnings
      from t
     ) t;

You don't provide sample data, but this version assumes that month is an incrementing integer that represents the month. You may need to adjust the range depending on the type.