Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save remcotolsma/d72c00d46a39f6bc3b8b0184e46cec09 to your computer and use it in GitHub Desktop.
Save remcotolsma/d72c00d46a39f6bc3b8b0184e46cec09 to your computer and use it in GitHub Desktop.
Query companies and the number of hosting subscriptions and users.
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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment