Query companies and the number of hosting subscriptions and users.

orbis-query-companies-number-hosting-subscriptions.sql

SELECT
	company.name,
	company_subscriptions.number_support_subbscriptions,
	company_users.number_company_users
FROM
	orbis_companies AS company
		LEFT JOIN
	(
		SELECT
			subscription.company_id,
			COUNT( subscription.id ) AS number_support_subbscriptions
		FROM
			orbis_subscriptions AS subscription
				INNER JOIN
			orbis_subscription_types AS product
					ON subscription.type_id = product.id
		WHERE
			(
				product.name IN (
					'Hosting S ~ 35% korting', -- 140
					'Hosting M ~ 25% korting', -- 141
					'Hosting L ~ 15% korting', -- 142
					'Savvii – Starter – Jaarlijks' -- 76
				)
					OR
				product.id IN (
					140,
					141,
					142,
					76
				)
			)
				AND
			(
				subscription.cancel_date IS NULL
					OR
				subscription.expiration_date > NOW()
			)
		GROUP BY
			subscription.company_id
	) AS company_subscriptions
			ON company_subscriptions.company_id = company.id
		LEFT JOIN
	(
		SELECT
			user_company_p2p.p2p_to AS company_post_id,
			COUNT( user_company_p2p.p2p_id ) AS number_company_users
		FROM
			wp_p2p AS user_company_p2p
		WHERE
			user_company_p2p.p2p_type = 'orbis_users_to_companies'
		GROUP BY
			user_company_p2p.p2p_to
	) AS company_users
			ON company_users.company_post_id = company.post_id
WHERE
	company_subscriptions.number_support_subbscriptions > 0
GROUP BY
	company.id
ORDER BY
	number_company_users
;