-- $1 = INTERVAL'1.5s' in the execution plan
INSERT INTO transformation.high_level_events(
	id,
	activation_id)
WITH RECURSIVE
	acts AS (
		/* This CTE should prevent duplicate physical tablescans.
		 * An order by as the plan suggests, seems worthless. The
		 * executed plan will will sort this CTE again in each iteration 
		 * of he_per_day.
		 */
		SELECT a.id AS activation_id,
			   e.day_id,
			   a.activation_count,
			   e.timestamp
		FROM transformation.activations a JOIN transformation.events e
			 USING(id)
		ORDER BY day_id, activatoin_count),
	he_per_day AS (
		SELECT a.activation_id,
			min_acts.day_id,
			min_acts.activation_count,
			a.timestamp,
			CASE
				WHEN a.timestamp <= p.timestamp + $1 THEN
					p.last_high_level_event
				ELSE a.activation_id
			END AS he_id -- identifies a specific high level event
		FROM (SELECT day_id,
					MIN(activation_count) AS activation_count
				FROM acts
				GROUP BY day_id) min_acts
			JOIN acts a
				USING (day_id, activation_count)
			-- check whether it belongs to the last high_level event processed
			LEFT OUTER JOIN transformation.processed_activations_cache p
				 USING (day_id)
		UNION ALL
		SELECT a.activation_id,
			a.day_id,
			a.activation_count,
			a.timestamp,
			CASE
				WHEN a.timestamp - s.timestamp <= $1 THEN
					s.he_id
				ELSE a.activation_id
			END AS he_id
		FROM acts a	JOIN he_per_day s
				ON a.day_id = s.day_id
					AND a.activation_count = s.activation_count + 1)
SELECT he_id AS id, activation_id
FROM he_per_day;