subquery_simple.sql

SELECT
    avg(array_length(events, 1)) AS event_average,
    hasdone
 FROM  (
SELECT
        subquery_1.user_id,
        array_agg(event) AS events,
        COALESCE(hasdone, 'Has not done paying') AS hasdone
FROM
	(
		(
			SELECT 
				raw_events_first.user_id, 
				raw_events_second.value_1,
				'action=>1'::text AS event,
				raw_events_second.time as event_time
			FROM
				raw_events_first, raw_events_second
			WHERE
				raw_events_first.user_id = raw_events_second.user_id 
				AND raw_events_second.value_2 = 5
		)
		UNION
		(
			SELECT 
				raw_events_first.user_id, 
				raw_events_second.value_1,
				'action=>2'::text AS event,
				raw_events_second.time as event_time
			FROM
				raw_events_first, raw_events_second
			WHERE
				raw_events_first.user_id = raw_events_second.user_id 
				AND raw_events_second.value_2 = 6
		)
	) as subquery_1
INNER JOIN
	(
			SELECT 
				raw_events_first.user_id, 
				raw_events_second.value_1,
				'Has done paying'::TEXT AS hasdone
			FROM
				raw_events_first, raw_events_second
			WHERE
				raw_events_first.user_id = raw_events_second.user_id 
				AND raw_events_second.value_2 = 7 AND raw_events_first.user_id = 5

	) as subquery_2
ON subquery_1.user_id = subquery_2.user_id 
GROUP BY hasdone, subquery_1.user_id) as subquery_top GROUP BY hasdone;