duckdb: ( df1.sql.select("*,(b=c)::int col1") .select("*,max(index) filter(col1=1) over(partition by a,col1) col2") .select("*,max(col2) over(partition by a) col3") .filter("index<=col3") .order("index") ).df()