另一个选项（对于bigquery标准sql）

# standardSQL

SELECT *, 0 AS marker FROM `project.dataset.table` WHERE item_count IS NULL
UNION ALL
SELECT *, IF(1 = ROW_NUMBER() OVER(PARTITION BY user ORDER BY date), 1, 0)
FROM `project.dataset.table` WHERE NOT item_count IS NULL
ORDER BY user, date

考虑：

select 
    t.*
    case when date = min(case when itemcount is not null then date end) over(partition by user order by date)
        then 1
        else 0
    end as marker
from mytable t

我不确定bigquery是否支持 minif() 作为窗口函数：

select 
    t.*
    case when date = minif(date, itemcount is not null) over(partition by user order by date)
        then 1
        else 0
    end as marker
from mytable