gistfile1.sql

SELECT
    COUNT(ranktrack_urls.id)
FROM
	ranktrack_urls
JOIN users ON ranktrack_urls.user_id = users.id
JOIN pools ON pools.id = (
	SELECT
		id
	FROM
		pools
	WHERE
		user_id = ranktrack_urls.user_id
	AND pools.removed = 0
	AND pools.service_id = 2
	UNION
		SELECT
			id
		FROM
			pools
		WHERE
			user_id = ranktrack_urls.user_id
		AND pools.removed = 0
		AND pools.service_id IN(1, 3)
		LIMIT 1
)
JOIN subscriptions ON ranktrack_urls.user_id = subscriptions.user_id
JOIN pivot_ranktrack_url_google_settings ON ranktrack_urls.id = pivot_ranktrack_url_google_settings.ranktrack_url_id
LEFT JOIN ranktrack_features ON(
	SELECT
		ranktrack_feature_id
	FROM
		pivot_ranktrack_features
	WHERE
		pool_id = pools.id
	AND removed = 0
)= ranktrack_features.id
LEFT JOIN ranktrack_google_languages ON ranktrack_google_languages.id =(
	SELECT
		ranktrack_google_language_id
	FROM
		pivot_ranktrack_url_google_settings
	WHERE
		removed = 0
	AND ranktrack_url_id = ranktrack_urls.id
)
LEFT JOIN ranktrack_google_tlds ON ranktrack_google_tlds.id =(
	SELECT
		ranktrack_google_tld_id
	FROM
		pivot_ranktrack_url_google_settings
	WHERE
		removed = 0
	AND ranktrack_url_id = ranktrack_urls.id
)
LEFT JOIN countries ON countries.cdh =(
	SELECT
		country_cdh
	FROM
		pivot_ranktrack_url_google_settings
	WHERE
		removed = 0
	AND ranktrack_url_id = ranktrack_urls.id
)
WHERE
ranktrack_urls.removed = 0
AND subscriptions.active = 1
AND users.removed = 0