SELECT ranked.subscriber,
       pcod
FROM (SELECT times_visited.subscriber,
             pcod,
             row_number() OVER (PARTITION BY times_visited.subscriber
                                ORDER BY total DESC) AS rank
      FROM (SELECT subscriber_locs.subscriber,
                   pcod,
                   count(*) AS total
            FROM (SELECT subscriber,
                         datetime AS time,
                         pcod
                  FROM (SELECT l.datetime,
                               l.subscriber,
                               l.location_id,
                               sites.pcod
                        FROM (SELECT events.calls.datetime,
                                     events.calls.imei AS subscriber,
                                     events.calls.location_id
                              FROM events.calls
                              WHERE (events.calls.datetime >= '2016-01-04 00:00:00')
                                AND (events.calls.datetime < '2016-01-05 00:00:00')
                                AND (to_char(events.calls.datetime, 'HH24:MI') >= '03:00')
                                AND (to_char(events.calls.datetime, 'HH24:MI') < '09:00')
                                AND events.calls.imei IN ('2GJxeNazvlgZbqj6', '7qKmzkeMbmk5nOa0', '8dpPLR15XwR7jQyN', '1NqnrAB9bRd597x2')

                              UNION ALL

                              SELECT events.sms.datetime,
                                     events.sms.imei AS subscriber,
                                     events.sms.location_id
                              FROM events.sms
                              WHERE (events.sms.datetime >= '2016-01-04 00:00:00')
                                AND (events.sms.datetime < '2016-01-05 00:00:00')
                                AND (to_char(events.sms.datetime, 'HH24:MI') >= '03:00')
                                AND (to_char(events.sms.datetime, 'HH24:MI') < '09:00')
                                AND events.sms.imei IN ('2GJxeNazvlgZbqj6', '7qKmzkeMbmk5nOa0', '8dpPLR15XwR7jQyN', '1NqnrAB9bRd597x2')) AS l
                             INNER JOIN (SELECT loc_table.id AS location_id,
                                                loc_table.date_of_first_service,
                                                loc_table.date_of_last_service,
                                                geom_table.admin2pcod AS pcod
                                         FROM infrastructure.cells AS loc_table
                                              INNER JOIN (SELECT gid,
                                                                 admin0name,
                                                                 admin0pcod,
                                                                 admin1name,
                                                                 admin1pcod,
                                                                 admin2name,
                                                                 admin2pcod,
                                                                 admin2refn,
                                                                 admin2altn,
                                                                 admin2al_1,
                                                                 date,
                                                                 validon,
                                                                 validto,
                                                                 shape_star,
                                                                 shape_stle,
                                                                 shape_leng,
                                                                 shape_area,
                                                                 geom
                                                          FROM geography.admin2) AS geom_table ON st_within(loc_table.geom_point::geometry,
                                                                                                            st_setsrid(geom_table.geom, 4326)::geometry)) AS sites ON (l.location_id = sites.location_id)
                                                                                                                                                                  AND (l.datetime)::date BETWEEN COALESCE(sites.date_of_first_service,
                                                                                                                                                                                                          ('-infinity')::timestamptz)
                                                                                                                                                                                             AND COALESCE(sites.date_of_last_service,
                                                                                                                                                                                                          ('infinity')::timestamptz)) AS foo
                  ORDER BY time) AS subscriber_locs
            GROUP BY subscriber_locs.subscriber, pcod) AS times_visited) AS ranked
WHERE rank = 1