I have a table like this:
| date | user_id | revenue |
|---|---|---|
| 2021-10-01 | 1 | 2 |
| 2021-10-02 | 1 | 3 |
| 2021-10-05 | 1 | 2 |
| 2021-10-09 | 1 | 3 |
| 2021-10-15 | 1 | 3 |
| 2021-10-01 | 2 | 2 |
| 2021-10-04 | 2 | 2 |
| 2021-10-10 | 2 | 1 |
| 2021-10-11 | 2 | 3 |
| 2021-10-11 | 2 | 3 |
| 2021-10-20 | 2 | 5 |
And I want to add column with median revenue for the last 5 days group by user_id. Desired output should looks like this:
| date | user_id | revenue | median_last_5_days |
|---|---|---|---|
| 2021-10-01 | 1 | 2 | NULL |
| 2021-10-02 | 1 | 3 | 2 |
| 2021-10-05 | 1 | 2 | 2.5 |
| 2021-10-09 | 1 | 3 | 2 |
| 2021-10-16 | 1 | 3 | NULL |
| 2021-10-01 | 2 | 2 | NULL |
| 2021-10-02 | 2 | 3 | 2 |
| 2021-10-03 | 2 | 3 | 2.5 |
| 2021-10-04 | 2 | 2 | 3 |
| 2021-10-10 | 2 | 1 | NULL |
| 2021-10-11 | 2 | 3 | 1 |
| 2021-10-11 | 2 | 3 | 2 |
| 2021-10-20 | 2 | 5 | NULL |
Can I produce this with SQL BigQuery? Thanks for helping me)
CodePudding user response：
Use of PERCENTILE_CONT or PERCENTILE_DISC to get the median will not work on these conditions as window_frame_clause are not allowed in 
Output:
