SELECT a.sum_date
FROM
(SELECT '2024-08-06' AS sum_date
FROM
(SELECT ident_nbr_md5
FROM tmp_lcy.tablex3
WHERE day_id = '2024-08-06'
AND apply_dt >= '2020-01-01'
AND partner_nm != 'xxxx'
GROUP BY ident_nbr_md5 ) a ) a
LEFT JOIN
(SELECT '2024-08-06' AS sum_date
FROM tmp_lcy.tablex1
WHERE day_id >= '2024-01-01' ) c
ON a.sum_date = c.sum_date ;
CREATE TABLE `tmp_lcy`.`tablex3` (
`apply_dt` STRING ,
`partner_nm` STRING ,
`ident_nbr_md5` STRING ,
`day_id` STRING,
`prod_cd` STRING)
USING orc
PARTITIONED BY (day_id, prod_cd);
CREATE TABLE `tmp_lcy`.`tablex1` (
`mobile_nbr` STRING ,
`day_id` STRING ,
`org` STRING )
USING orc
PARTITIONED BY (day_id, org);
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT day_id FROM tmp_lcy.tablex3 a LEFT JOIN (SELECT '2024-08-06' AS sum_date FROM tmp_lcy.tablex1 WHERE day_id = '2024-01-01' ) c ON a.day_id = c.sum_date ; SELECT a.day_id FROM tmp_lcy.tablex3 a LEFT JOIN tmp_lcy.tablex1 c ON a.day_id = c.day_id ; CREATE TABLE `tmp_lcy`.`tablex3` ( `apply_dt` STRING , `partner_nm` STRING , `ident_nbr_md5` STRING , `day_id` STRING, `prod_cd` STRING) USING orc PARTITIONED BY (day_id, prod_cd); CREATE TABLE `tmp_lcy`.`tablex1` ( `mobile_nbr` STRING , `day_id` STRING , `org` STRING ) USING orc PARTITIONED BY (day_id, org);