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(CAST(loc_table.geom_point AS geometry),
                                                                                                            CAST(st_setsrid(geom_table.geom, 4326) AS geometry))) AS sites ON l.location_id = sites.location_id
                                                                                                                                                                          AND CAST(l.datetime AS date) BETWEEN COALESCE(sites.date_of_first_service,
                                                                                                                                                                                                                        CAST('-infinity' AS timestamptz))
                                                                                                                                                                                                           AND COALESCE(sites.date_of_last_service,
                                                                                                                                                                                                                        CAST('infinity' AS timestamptz))) AS foo
                  ORDER BY time) AS subscriber_locs
            GROUP BY subscriber_locs.subscriber, pcod) AS times_visited) AS ranked
WHERE rank = 1
