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 events.calls.datetime,
                               events.calls.location_id,
                               events.calls.msisdn AS subscriber
                        FROM events.calls
                        WHERE (events.calls.datetime >= '2016-01-01 00:00:00')
                          AND (events.calls.datetime < '2016-01-02 00:00:00')

                        UNION ALL

                        SELECT events.sms.datetime,
                               events.sms.location_id,
                               events.sms.msisdn AS subscriber
                        FROM events.sms
                        WHERE (events.sms.datetime >= '2016-01-01 00:00:00')
                          AND (events.sms.datetime < '2016-01-02 00:00:00')) 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(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
            WHERE (location_id IS NOT NULL)
              AND (location_id <> '')) AS subscriber_locs) AS final_time
WHERE rank = 1