All current paying UFB users

gistfile1.txt

with paid_orgs as  (
SELECT
  o.id

FROM organization_has_package ohp, package p, organization o, payment_plan pp
WHERE o.isdeleted='false'
AND o.istest='false'
AND ohp.organizationid=o.id
AND p.subscriptiontype = 'content'
AND ohp.id = (
      -- this is how we pick the active package in Django
      SELECT ohp1.id
      FROM raw_data.organization_has_package ohp1
      LEFT OUTER JOIN raw_data.organization_has_package ohp2 ON (ohp1.organizationid = ohp2.organizationid)
      WHERE ohp1.organizationid = o.id
      GROUP BY ohp1.id, ohp1.organizationid, ohp1.startdate

      HAVING ohp1.startdate < GETDATE() and MIN(CASE
        WHEN (ohp1.id < (ohp2.id) AND ohp1.startdate <= (ohp2.startdate))
        THEN LEAST(
          COALESCE(ohp1.cancellationdate, ohp1.enddate),
          ohp1.enddate,
          ohp2.startdate
        )
        WHEN (ohp1.id < (ohp2.id) AND ohp1.startdate > (ohp2.startdate))
        THEN ohp1.startdate
        WHEN ohp1.cancellationdate < (ohp1.startdate)
        THEN ohp1.startdate
        ELSE LEAST(
          COALESCE(ohp1.cancellationdate, ohp1.enddate), ohp1.enddate)
      END) > GETDATE()
)

AND ohp.packageid=p.id
AND pp.id = p.paymentplanid
AND p.maxusercount > 0

)

select count(*)

from organization_has_user as ohu

where ohu.organizationid in (select id from paid_orgs);