gistfile1.sql

SELECT s.id as SubID, s.subdomain AS Subdomain, s.name AS Company, u.email AS UEmail, u.first_name AS First_name, u.last_name AS Last_name, 
subs.subscription_plan_id AS PlanID, rp.amount AS Amount, s.created_at AS Created_At, current_login_at AS Last_Login, i_s.mtdb_id AS MtdbID, s.identity AS Identity
FROM users u
JOIN subscribers s ON s.id = u.subscriber_id
JOIN infrastructure_production.subscribers i_s ON i_s.id = s.id AND i_s.referral_token_id != 4
JOIN infrastructure_production.subscriptions subs ON i_s.id = subs.subscriber_id
JOIN infrastructure_production.subscription_plans sub_p ON sub_p.id = subs.subscription_plan_id AND sub_p.id NOT IN (6,12) AND sub_p.category != 'beta'
JOIN infrastructure_production.renewal_periods rp ON rp.id = renewal_period_id
WHERE UPPER(s.subdomain) NOT LIKE 'NBB'
AND u.admin = 1
AND u.email IS NOT NULL
AND u.last_request_at > DATE_SUB(CURRENT_DATE(),INTERVAL 60 DAY)
AND u.email NOT LIKE '%earn.com' AND u.email NOT LIKE '%cscinfo.com' AND u.email NOT LIKE '%netbooks.com' AND u.email NOT LIKE '%cmea.com' AND u.email NOT LIKE '%[email protected]' AND u.email NOT LIKE '%jessicamah.com' AND u.email NOT LIKE '%outright.com' AND u.email NOT LIKE '%freshbooks.com' 
AND s.deleted_at IS NULL
AND DATE_FORMAT(CONVERT_TZ(s.created_at, 'GMT', 'US/Pacific'),'%Y%m%d') >= 20090331
AND rp.amount = 0 AND s.segments NOT LIKE '%active%'
GROUP BY s.subdomain
ORDER BY s.identity