SELECT final_time.subscriber,
       pcod
FROM (SELECT subscriber_locs.subscriber,
             time,
             pcod,
             row_number() OVER (PARTITION BY subscriber_locs.subscriber
                                ORDER BY time DESC) AS rank
      FROM (SELECT subscriber,
                   datetime AS time,
                   pcod
            FROM (SELECT l.datetime,
                         l.location_id,
                         l.subscriber,
                         sites.pcod
                  FROM (SELECT tbl.datetime,
                               tbl.location_id,
                               tbl.subscriber
                        FROM (SELECT events.calls.datetime AS datetime,
                                     events.calls.location_id AS location_id,
                                     events.calls.msisdn AS subscriber
                              FROM events.calls
                              WHERE events.calls.datetime >= '2016-01-05 00:00:00'
                                AND events.calls.datetime < '2016-01-06 00:00:00'
                                AND (to_char(events.calls.datetime, 'HH24:MI') < '06:00'
                                  OR to_char(events.calls.datetime, 'HH24:MI') >= '22:00')) AS tbl
                             INNER JOIN (SELECT *
                                         FROM ((VALUES ('dr9xNYK006wykgXj'))) AS tmp (subscriber)) AS subset_query ON tbl.subscriber = subset_query.subscriber) 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.admin3pcod AS pcod
                                   FROM infrastructure.cells AS loc_table
                                        INNER JOIN (SELECT gid,
                                                           admin0name,
                                                           admin0pcod,
                                                           admin1name,
                                                           admin1pcod,
                                                           admin2name,
                                                           admin2pcod,
                                                           admin3name,
                                                           admin3pcod,
                                                           admin3refn,
                                                           admin3altn,
                                                           admin3al_1,
                                                           date,
                                                           validon,
                                                           validto,
                                                           shape_star,
                                                           shape_stle,
                                                           shape_leng,
                                                           shape_area,
                                                           geom
                                                    FROM geography.admin3) 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
            WHERE location_id IS NOT NULL
              AND location_id <> '') AS subscriber_locs) AS final_time
WHERE rank = 1
