For each row, I want to take average of last 20 non-null values using.

The window function is taking 20 rows below including the null ones and calculating average, while I want average of last 20 non null rows.

**What I have tried?**

```
WITH adv_calculated AS (
    SELECT security_id                                                                               AS sec_id,
           date                                                                                      AS pd,
           AVG(volume)
           OVER (PARTITION BY (security_id) ORDER BY date ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS adv
    FROM tbl_financial_index_data
    WHERE volume IS NOT NULL
    GROUP BY security_id, date
)
UPDATE tbl_financial_index_data
SET adv                  = amc.adv
FROM adv_calculated amc
WHERE amc.sec_id = security_id
  AND amc.pd = date

```

This solution works good for all rows where volume is NOT null but it does not calculate the adv/average for the rows where volume is NULL.
Then for the null adv and volume rows, I have to run this query which is really slow

```
UPDATE tbl_financial_index_data
SET average_daily_volume =
        (SELECT avg(t.volume)
         FROM (
                  SELECT a.volume
                  FROM tbl_financial_index_data a
                  WHERE a.security_id = tbl_financial_index_data.security_id
                    AND a.date::date <= tbl_financial_index_data.date::date
                    AND a.volume IS NOT NULL
                  ORDER BY a.date DESC
                  LIMIT 21
              ) t)
WHERE volume IS NULL;

```

I want to avoid using the second query and calculate ADV for all rows using first query (because it is much faster).