可以使用窗口函数：

select t.*,
       coalesce(last_value(case when event = 'Upgrade' then new_product end ignore nulls) over (partition by sub_id order by created),
                first_value(original_product ignore nulls) over (partition by sub_id order by created)
               ) as desired_value
from t;

这是最近的 new_product 从“升级”行。如果这不存在，那么它得到的是整体 original_product .

我想你想要 first_value() :

select 
    t.*,
    coalesce(
        first_value(new_product ignore nulls) over(
            order by created desc
            rows between unboundeed preceding and current row
        ),
        first_value(original_product ignore nulls) over(
            order by created
            rows between current row and unbounded following 
        )
    ) desired_value
from mytable t

其思想是首先尝试获取第一个非空值 new_product 前面行上的值（包括当前行）。如果没有这样的行，那么我们将查找第一个非空的行 original product 在下面的行中。
理论上，你还需要一个 partition by 包含表示用户的列的子句。但是你的数据没有这种列的迹象，所以我把它分开了。

下面是bigquery标准sql

# standardSQL

SELECT *, 
  IFNULL(
    FIRST_VALUE(original_product IGNORE NULLS) OVER(original_product_lookup),
    FIRST_VALUE(new_product IGNORE NULLS) OVER(new_product_lookup)
  ) AS desired_value
FROM `project.dataset.table` 
WINDOW 
  original_product_lookup AS (ORDER BY created ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),
  new_product_lookup AS (ORDER BY created DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

您可以使用问题中的简化数据（仅使用已使用/相关的数据点）进行测试、玩上述游戏，如下面的示例所示

# standardSQL

WITH `project.dataset.table` AS (
  SELECT 1 created, NULL original_product, NULL new_product UNION ALL
  SELECT 2, NULL, NULL UNION ALL
  SELECT 3, 'Level 1', 'Level 2' UNION ALL
  SELECT 4, NULL, NULL UNION ALL
  SELECT 5, 'Level 2', 'Level 1' UNION ALL
  SELECT 6, NULL, NULL 
)
SELECT *, 
  IFNULL(
    FIRST_VALUE(original_product IGNORE NULLS) OVER(original_product_lookup),
    FIRST_VALUE(new_product IGNORE NULLS) OVER(new_product_lookup)
  ) AS desired_value
FROM `project.dataset.table` 
WINDOW 
  original_product_lookup AS (ORDER BY created ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),
  new_product_lookup AS (ORDER BY created DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
ORDER BY created

有结果的

Row created original_product    new_product desired_value    
1   1       null                null        Level 1  
2   2       null                null        Level 1  
3   3       Level 1             Level 2     Level 2  
4   4       null                null        Level 2  
5   5       Level 2             Level 1     Level 1  
6   6       null                null        Level 1

能够用答案的组合来解决问题

SELECT e.*, 
       coalesce(
        last_value(case when (event  ='Upgrade' OR event = "Downgrade" OR event = "Crossgrade") then new_product end ignore nulls) over (partition by subscription order by created),

         first_value(original_product ignore nulls) over(
            order by created
            rows between current row and unbounded following 
        )
    ) desired_value

FROM  e