instances_v01.sql 550 B

1234567891011121314151617
  1. WITH domain_counts(domain, accounts_count)
  2. AS (
  3. SELECT domain, COUNT(*) as accounts_count
  4. FROM accounts
  5. WHERE domain IS NOT NULL
  6. GROUP BY domain
  7. )
  8. SELECT domain, accounts_count
  9. FROM domain_counts
  10. UNION
  11. SELECT domain_blocks.domain, COALESCE(domain_counts.accounts_count, 0)
  12. FROM domain_blocks
  13. LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_blocks.domain
  14. UNION
  15. SELECT domain_allows.domain, COALESCE(domain_counts.accounts_count, 0)
  16. FROM domain_allows
  17. LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_allows.domain