Sum of Pins that intersect with the current district on the given category id

get_category_zone.sql

DROP FUNCTION IF EXISTS get_category_zone(integer, text);

CREATE OR REPLACE FUNCTION get_category_zone(cat_id integer, zone_id text) RETURNS numeric AS $$
  SELECT SUM(zone_list.count) FROM (
    SELECT 
      d.id,
      d.zone,
      (
        SELECT COUNT(*)
        FROM pins p0
        JOIN demands d0 ON p0.demand_id = d0.id
        WHERE ST_Intersects(
          d.geo,
          ST_MakePoint(p0.long::numeric, p0.lat::numeric)
        )
        AND d0.category_id = cat_id
        AND d0.created_at < '2015-06-01'::timestamp
      )
    FROM districts d
  ) as zone_list
  WHERE zone_list.zone = zone_id
$$ LANGUAGE SQL;


SELECT 
  c0.id,
  c0.name,
  (
    SELECT count(*) from demands d1 where d1.category_id = c0.id 
    AND d1.created_at < '2015-06-01'::timestamp
  ), 
  get_category_zone(c0.id, 'AP1') as AP1,
  get_category_zone(c0.id, 'AP2') as AP2,
  get_category_zone(c0.id, 'AP3') as AP3,
  get_category_zone(c0.id, 'AP4') as AP4,
  get_category_zone(c0.id, 'AP5') as AP5
FROM categories c0
GROUP BY 1;