-- Behavior of Adopted Customers

SELECT
	--count(distinct location_id)
	*
FROM
	(SELECT
	        employees.email as office_manager_email,
	        customers.name as customer_name,
	        locations.address as location_address,
	        employees.created_at as first_invitation_sent_date,
	        employees.invitation_accepted_at as first_login_date,
	        locations.id as location_id,
	        shift_notes_count,
	        review_count,
	        tasks_created
	FROM
	        employees
	INNER JOIN
	        (SELECT
	            employees.id as employee_id,
	            count(comments.id) as shift_notes_count
	        from
	            employees
	        left join
	            comments on commenter_id = employees.id
	        and
	            commenter_type = 'Employee'
	        and
	            commentable_type = 'Cleaning'
	        where
	            employees.role = 'office_manager'
	        group by employees.id)
	        as shift_notes ON shift_notes.employee_id = employees.id
	INNER JOIN
		(SELECT
			employees.id as employee_id,
			count(reviews.id) as review_count
		FROM
			employees
		LEFT JOIN
			reviews on reviews.employee_id = employees.id
		GROUP BY
			employees.id)
		AS reviews_left ON reviews_left.employee_id = employees.id
	INNER JOIN
		(SELECT
			employees.id as employee_id,
			count(task_events.id) as tasks_created
		FROM
			employees
		LEFT JOIN
			task_events on task_events.actor_id = employees.id
		AND
			actor_type = 'employee'
		AND
			event_action = 'create'
		GROUP BY
			employees.id)
		AS tasks_created ON tasks_created.employee_id = employees.id
	INNER JOIN
	        employees_locations ON employees.id = employees_locations.employee_id
	INNER JOIN
	        locations ON employees_locations.location_id = locations.id
	INNER JOIN
	        customers ON locations.customer_id = customers.id
	WHERE
	        employees.role = 'office_manager'
	AND
	        employees.created_at > '2017.09.13') as foo;