Postgresql – PostgresSQL (AWS Redshift) Forward-fill null values based on previous non-null value in same column

Your method doesn’t seem wrong but it might be handling strange cases in a way you didn’t expect it to:

1. The window definition in your subquery is missing a partition by. The fact that customer_id is on the first position of its order by helps because different customers are never in the same peer group.

   Thing is, it’s overlooking the fact that higher customer_id‘s see
   all preceding customers. This means a series of orders with a
   leading null would fall back on sub_id from some completely
   unrelated customer with a lower identifier.

2. Your outer query window frame spec is too narrow. If there are multiple orders from the same customer and date, some null and some not, only one of them will have the full picture to be able to pick one of the not-null values. You can fix it by expanding it to unbounded following.

3. The outer query window only needs to be ordered by sub_id nulls last. You already established sub-partitions with at most a single not-null in them, now you just need to make sure it comes first if you want first_value() to pick it up.
   Even though Redshift Unsupported PostgreSQL features lists this:

   NULLS clause in Window functions

   I don’t think they mean nulls last|first because it’s clearly in Redshift window function syntax:

   The order_list is as follows.

   expression | column_name [ ASC | DESC ] 
   [ NULLS FIRST | NULLS LAST ]
   [, order_list ]
   

   So that might be about nulls [not] distinct. Still, if the clause were to get rejected, null values should come last by default:

   By default, NULL values are sorted and ranked last in ASC ordering

   Unlike in PostgreSQL, there’s also an option to ignore nulls in Redshift, which saves you the whole nested window function call.

_{demo at db<>fiddle}

select*,first_value(sub_id)
          over(partition by customer_id, value_partition 
               order by sub_id nulls last
               rows between unbounded preceding 
                        and unbounded following) as forward_filled_sub_id
from(select customer_id
          , order_id
          , sub_id
          , date
          , count(sub_id)
              over(partition by customer_id
                   order by date
                   rows between unbounded preceding 
                            and current row) as value_partition
     from m.parent_child_lvl_1 
     where customer_id in (  '108384372','222222222','333333333','444444444'
                           , '555555555','666666666','777777777','888888888' )
     and order_type in (  'sub_parent_first_order'
                        , 'sub_parent_order'
                        , 'sub_order')
    ) as subquery
order by customer_id
       , date
       , order_id;

4. You can do this sort of thing:

   SUM(CASE WHEN sub_id IS NULL THEN 0 ELSE 1 END)
   

   a bit more efficiently using an aggregate filter clause:

   count(*)filter(where sub_id is not null)
   

   But for this particular use case, there’s a dedicated variant of count():

   count(sub_id)
   

   count ( "any" ) → bigint
   Computes the number of input rows in which the input value is not null.

   The count(expression) is available in both PostgreSQL and Redshift but unfortunately Redshift doesn’t seem to support the filter clause. It offers a qualify, but that filters things going out of the function, not what’s fed into it.

5. Whenever your window has an ORDER BY, the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is implied in PostgreSQL:

   The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, this sets the frame to be all rows from the partition start up through the current row’s last ORDER BY peer.

   Unfortunately, that’s not the case in Redshift:

   If an ORDER BY clause is used for an aggregate function, an explicit frame clause is required.

6. Defining the window in its dedicated section instead of right in over() lets you re-use the same window definition multiple times and tidies up the select section a bit. You can also base other windows off of pre-defined windows: _{demo at db<>fiddle}

   select item_id
        , item_category
        , sum(weight)over w1 as "total weight in its category"
        , sum(volume)over w1 as "total volume in its category"
        , sum(weight)over(w1 order by date) as "stepping weight in its category" 
   from test
   window w1 as (partition by item_category);
   

   Unfortunately, Redshift doesn’t support it.