Q:

Query giving wrong results.  Any idea why is this happening? 

The query is not returning any rows despite running successfully in ETL Manager.  Any idea why this is happening?  In case you think the first two temp tables might contain zero results, I can assure that they don't since I have executed each sections separately.  
 CREATE TEMPORARY TABLE T1  AS /* This is to get top 1000 address ids which are unique identifiers for addresses in terms of orders frequency which is decided by number of distinct ordering order ids */
    SELECT destination_address_id 
    ,COUNT(DISTINCT ordering_order_id)a
    FROM oe
    where shipment_status = 'DELIVERED'              
    AND delivery_station_code = 'DCH1'
    AND event_day BETWEEN '2018-10-01' AND '2018-12-31'
    AND tracking_id IS NOT NULL
    GROUP BY destination_address_id
    ORDER BY a DESC
    LIMIT 1000
    ;

    CREATE TEMPORARY TABLE T1_2   AS /* This is to get tracking ids corresponding to those top 1000 address ids */
    SELECT DISTINCT destination_address_id
    ,tracking_id
    FROM oe
    WHERE destination_address_id IN (SELECT destination_address_id FROM T1) 
    AND event_day BETWEEN '2018-10-01' AND '2018-12-31'
    AND shipment_status = 'DELIVERED'
    AND delivery_station_code = 'DCH1'
    AND tracking_id IS NOT NULL

    ;
    SELECT DISTINCT lat1
    ,long1
    ,external_address_id destination_address_id
    ,tracking_id
    ,actual_lat
    ,actual_long
    ,ROW_NUMBER() OVER(PARTITION BY tracking_id ORDER BY deliverydate DESC) rn 
    FROM gdd
    WHERE shipment_status_id in (51,'DELIVERED')
    AND tracking_id IN(SELECT tracking_id FROM T1_2)
    AND confidence1 = 'high'
    AND station_code='DCH1'
    AND deliverydate BETWEEN TO_DATE('2018-10-01','YYYY-MM-DD') AND TO_DATE('2018-12-31','YYYY-MM-DD')
    AND actual_lat IS NOT NULL
    AND actual_long IS NOT NULL

A:

Check for unexpected formatting differences between the tables.  
Recommend expressing the following section as a JOIN instead of an IN list.  Then you can temporarily change it to a left join to see whether any rows are matched. 
Your SQL:
…
FROM gdd
WHERE shipment_status_id in (51,'DELIVERED')
AND tracking_id IN(SELECT tracking_id FROM T1_2)
…

JOIN SQL:
…
FROM gdd
{ INNER | LEFT } JOIN T1_2 ON gdd.tracking_id = T1_2.tracking_id
WHERE shipment_status_id in (51,'DELIVERED')
…

