MySQL Subscriber Queries

/** Current Subscribers **/
SELECT COUNT(sub.user_id) as current_subscribers, plan.title
FROM `xiszt_payplans_subscription` sub
LEFT JOIN `xiszt_payplans_plan` plan ON plan.plan_id = sub.plan_id
WHERE sub.status = 1601
GROUP BY sub.plan_id;

/** Expired Subscribers **/
SELECT COUNT(sub.user_id) as expired_subscribers, plan.title
FROM `xiszt_payplans_subscription` sub
LEFT JOIN `xiszt_payplans_plan` plan ON plan.plan_id = sub.plan_id
WHERE sub.status = 1603
AND sub.user_id NOT IN (SELECT s.user_id FROM `xiszt_payplans_subscription` s WHERE s.status = 1601)
GROUP BY sub.plan_id;

/** Renewals by COUNT(user_id) **/
SELECT COUNT(sub.user_id) as renewals, plan.title
FROM `xiszt_payplans_subscription` sub
LEFT JOIN `xiszt_payplans_plan` plan ON plan.plan_id = sub.plan_id
WHERE sub.expiration_date > DATE_ADD(sub.subscription_date, INTERVAL 1 YEAR)
AND sub.expiration_date > NOW()
GROUP BY sub.plan_id;

/** User Count **/
SELECT COUNT(id) as user_count
FROM `xiszt_users`;

/** Uploaded Files **/
SELECT COUNT(id) as file_count FROM `xiszt_jbackend_logs_uploads`;

/* Subscribers ACTIVE */
SELECT sub.user_id FROM xiszt_payplans_subscription sub
WHERE sub.status = '1601'
AND (plan_id = 10 OR plan_id = 2)
AND sub.user_id != '102'
GROUP BY user_id;

/* Subscribers EXPIRED */
SELECT sub.user_id FROM xiszt_payplans_subscription sub
WHERE sub.status = '1603'
AND (plan_id = 10 OR plan_id = 2)
AND sub.user_id NOT IN (SELECT s.user_id FROM xiszt_payplans_subscription s WHERE status = '1601' AND (s.plan_id = 10 OR s.plan_id = 2) GROUP BY s.user_id)
AND sub.user_id != '102'
GROUP BY sub.user_id;

/* Subscribers ALL */
SELECT sub.user_id FROM xiszt_payplans_subscription sub
WHERE (plan_id = 10 OR plan_id = 2)
AND (sub.status = '1601' OR sub.status = '1603')
AND sub.user_id != '102'
GROUP BY sub.user_id;


/* Contributors ALL */
SELECT up.userid FROM xiszt_jbackend_logs_uploads up
WHERE up.time >= NOW()-INTERVAL 4 MONTH
GROUP BY up.userid
ORDER BY up.userid;

/* Users (ALL) */
SELECT ju.id
FROM xiszt_users ju
LEFT JOIN xiszt_user_usergroup_map g ON ju.id = g.user_id
WHERE g.group_id != '8'
AND ju.id != '102'
GROUP BY ju.id;