Union_Query

insert overwrite TABLE test.test_tez_multiunion
SELECT   aver,
         asub ,
         from_unixtime(unix_timestamp(snapshot_date_time, 'yyyy-MM-dd')) AS snapshot_date_time ,
         count (DISTINCT l_id)                                     AS a_lists , (
         CASE
                  WHEN (
                                    psla>=0
                           AND      fsp>0
                           AND      lis='1') THEN count (DISTINCT l_id)
                  ELSE 0
         END)                     AS active_instock_listings ,
         count (DISTINCT iid) AS a_styles , (
         CASE
                  WHEN (
                                    psla>=0
                           AND      fsp>0
                           AND      lis='1') THEN count (DISTINCT iid)
                  ELSE 0
         END)                   AS a_i_styles
FROM     (
                SELECT
                       l_id,
                       psla,
                       fsp,
                       lis,
                       aver,
                       asub ,
                       iid,
                       date_sub(to_date(from_unixtime(unix_timestamp())),120) AS snapshot_date_time
                FROM   test.test_tez_input
                WHERE  to_date(start_date_time)<= date_sub(to_date(from_unixtime(unix_timestamp())),120)
                AND    (
                              to_date(end_date_time)> date_sub(to_date(from_unixtime(unix_timestamp())),120)
                       OR     end_date_time IS NULL)
                UNION ALL
                SELECT
                       l_id,
                       psla,
                       fsp,
                       lis,
                       aver,
                       asub ,
                       iid,
                       date_sub(to_date(from_unixtime(unix_timestamp())),120) AS snapshot_date_time
                FROM   test.test_tez_input
                WHERE  to_date(start_date_time)<= date_sub(to_date(from_unixtime(unix_timestamp())),120)
                AND    (
                              to_date(end_date_time)> date_sub(to_date(from_unixtime(unix_timestamp())),120)
                       OR     end_date_time IS NULL)
                UNION ALL
                              SELECT
                       l_id,
                       psla,
                       fsp,
                       lis,
                       aver,
                       asub ,
                       iid,
                       date_sub(to_date(from_unixtime(unix_timestamp())),120) AS snapshot_date_time
                FROM   test.test_tez_input
                WHERE  to_date(start_date_time)<= date_sub(to_date(from_unixtime(unix_timestamp())),120)
                AND    (
                              to_date(end_date_time)> date_sub(to_date(from_unixtime(unix_timestamp())),120)
                       OR     end_date_time IS NULL)
                UNION ALL
                               SELECT
                       l_id,
                       psla,
                       fsp,
                       lis,
                       aver,
                       asub ,
                       iid,
                       date_sub(to_date(from_unixtime(unix_timestamp())),120) AS snapshot_date_time
                FROM   test.test_tez_input
                WHERE  to_date(start_date_time)<= date_sub(to_date(from_unixtime(unix_timestamp())),120)
                AND    (
                              to_date(end_date_time)> date_sub(to_date(from_unixtime(unix_timestamp())),120)
                       OR     end_date_time IS NULL)
                        ) listing_snapshot
GROUP BY aver,
         asub,
         snapshot_date_time,
         lis,
         psla,
         fsp;