-- Query that count the number of contacts in a interval and
-- divides by the total of contacts

COPY(
WITH filtered_fnu AS (
    SELECT
        id_city
        , EXTRACT(HOUR FROM collect_time) AS "hour"
        , COUNT(1) AS "contacts"
    FROM
        fact_net_usage
    WHERE
        collect_time >= '${TIME_FILTER_INIT}'::time
        AND collect_time < '${TIME_FILTER_END}'::time
        AND collect_date >= '${DATE_FILTER_INIT}'::date
        AND collect_date < '${DATE_FILTER_END}'::date
        AND EXTRACT(DOW FROM collect_date) != 0
        AND EXTRACT(DOW FROM collect_date) != 6
    GROUP BY
        id_city
        , hour
), total_by_city AS (
    SELECT
        id_city
        , SUM(contacts) AS "total_contacts"
    FROM
        filtered_fnu
    GROUP BY
        id_city
), percentage_by_city AS (
    SELECT
        f.id_city
        , f.hour
        , (f.contacts/t.total_contacts) AS "percent_contacts"
    FROM
        filtered_fnu f
        INNER JOIN  total_by_city t ON f.id_city = t.id_city

), horizontal_percentage_by_city AS (
    SELECT
        id_city
        , CASE
            WHEN hour = 8 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_8
        , CASE
            WHEN hour = 9 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_9
        , CASE
            WHEN hour = 10 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_10
        , CASE
            WHEN hour = 11 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_11
        , CASE
            WHEN hour = 12 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_12
        , CASE
            WHEN hour = 13 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_13
        , CASE
            WHEN hour = 14 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_14
        , CASE
            WHEN hour = 15 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_15
        , CASE
            WHEN hour = 16 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_16
        , CASE
            WHEN hour = 17 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_17
    FROM
        percentage_by_city
), sumarize_horizontal_percentage_by_city AS (
    SELECT
        id_city AS "COD IBGE 7"
        , SUM(percent_contacts_8) AS "Porcentagem de contatos (8)"
        , SUM(percent_contacts_9) AS "Porcentagem de contatos (9)"
        , SUM(percent_contacts_10) AS "Porcentagem de contatos (10)"
        , SUM(percent_contacts_11) AS "Porcentagem de contatos (11)"
        , SUM(percent_contacts_12) AS "Porcentagem de contatos (12)"
        , SUM(percent_contacts_13) AS "Porcentagem de contatos (13)"
        , SUM(percent_contacts_14) AS "Porcentagem de contatos (14)"
        , SUM(percent_contacts_15) AS "Porcentagem de contatos (15)"
        , SUM(percent_contacts_16) AS "Porcentagem de contatos (16)"
        , SUM(percent_contacts_17) AS "Porcentagem de contatos (17)"
    FROM
        horizontal_percentage_by_city
    GROUP BY
        id_city
)
SELECT * FROM sumarize_horizontal_percentage_by_city

) TO STDOUT WITH CSV HEADER DELIMITER ';';