CREATE TABLE x
(
u bigint,
t string,
st string
)
PARTITIONED BY (`date` string)
STORED AS ORC 
TBLPROPERTIES ("orc.compress"="ZLIB");

CREATE TABLE y
(
u bigint
)
PARTITIONED BY (`date` string)
STORED AS ORC 
TBLPROPERTIES ("orc.compress"="ZLIB");

CREATE TABLE z
(
u bigint
)
PARTITIONED BY (`date` string)
STORED AS ORC 
TBLPROPERTIES ("orc.compress"="ZLIB");

CREATE TABLE v
(
t string, 
st string,
id int
)
STORED AS ORC 
TBLPROPERTIES ("orc.compress"="ZLIB");

EXPLAIN 
SELECT o.u, n.u
FROM 
(
SELECT m.u, Min(`date`) as ft
FROM 
(
SELECT u, `date` FROM x WHERE `date` < '2014-09-02' 
UNION ALL
SELECT u, `date` FROM y WHERE `date` < '2014-09-02' 
UNION ALL
SELECT u, `date` FROM z WHERE `date` < '2014-09-02' 
) m
GROUP BY m.u
) n 
LEFT OUTER JOIN
(
SELECT x.u
FROM x
JOIN v 
ON (x.t = v.t AND x.st <=> v.st)
WHERE x.`date` >= '2014-03-04' AND x.`date` < '2014-09-03'
GROUP BY x.u
) o
ON n.u = o.u 
WHERE n.u <> 0 AND n.ft <= '2014-09-02';

SELECT o.u, n.u
FROM 
(
SELECT m.u, Min(`date`) as ft
FROM 
(
SELECT u, `date` FROM x WHERE `date` < '2014-09-02' 
UNION ALL
SELECT u, `date` FROM y WHERE `date` < '2014-09-02' 
UNION ALL
SELECT u, `date` FROM z WHERE `date` < '2014-09-02' 
) m
GROUP BY m.u
) n 
LEFT OUTER JOIN
(
SELECT x.u
FROM x
JOIN v 
ON (x.t = v.t AND x.st <=> v.st)
WHERE x.`date` >= '2014-03-04' AND x.`date` < '2014-09-03'
GROUP BY x.u
) o
ON n.u = o.u 
WHERE n.u <> 0 AND n.ft <= '2014-09-02';