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

user_14042099 0
Viewed 550

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.

google-bigquery sql
1 Answers

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.

user_11440350
Related