-- 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 ';';