Using SQL window functions to replicate Google Analytics custom dimensions behavior

ga4_session_scope_custom_dimension.sql

LAST_VALUE(
  (SELECT 
     value.string_value --value.int_value, value.float_value, value.double_value
   FROM 
   	 UNNEST(event_params)
   WHERE 
   	 key = 'name_of_event_parameter_to_use_as_dimension'
  )
  IGNORE nulls 
) OVER (
	PARTITION BY
		user_pseudo_id,
  		(SELECT 
     		value.int_value
   		FROM 
   	 		UNNEST(event_params)
   		WHERE 
   	 		key = 'ga_session_id'
  		)
	ORDER BY 
		event_timestamp
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS my_custom_dimension