-- Query to detect pirate (unidentified) net usage COPY ( WITH temporal_fact_net_usage AS ( SELECT id_city , id_point , SUM(up_bytes) AS sum_up_bytes , SUM(down_bytes) AS sum_down_bytes FROM fact_net_usage WHERE collect_date >= '${REJECT_INIT_DATE}'::date AND collect_date < '${REJECT_END_DATE}'::date AND id_city IS NOT NULL AND id_point IS NOT NULL GROUP BY id_city , id_point ), active_city AS ( SELECT p.id_city , COUNT(CASE WHEN f.id_point IS NOT NULL AND f.sum_up_bytes > 0 AND f.sum_down_bytes > 0 THEN 1 ELSE NULL END) AS active , COUNT(1) AS total FROM point p LEFT OUTER JOIN temporal_fact_net_usage f ON p.id = f.id_point WHERE p.is_active AND p.is_digital_city GROUP BY p.id_city ), city_stats2 AS ( SELECT c.state , c.name AS "city_name" , c.city_code , a.active AS "active_points" , a.total AS "total_points" , (a.total - a.active) AS "inactive_points" FROM active_city a INNER JOIN city c on a.id_city = c.id ) SELECT * FROM city_stats2 ORDER BY state, city_name, city_code ) TO STDOUT WITH CSV HEADER DELIMITER ';';