title: Optimize a long running hive SQL query - has a join with same table
body: <p>The below query takes a lot of time to execute. It is run with tez execution engine.  </p>
  
  <pre><code>SELECT STG.EMP_TYPE, DEPT, A.TOTAL_COUNT, COUNT(DISTINCT EMP_ID) AS COUNT_DEPT
  FROM 
  STAGE_SOURCE STG 
  LEFT OUTER JOIN 
  ( SELECT EMP_TYPE, COUNT(DISTINCT EMP_ID) AS TOTAL_COUNT 
    FROM STAGE_SOURCE 
    GROUP BY EMP_TYPE 
  ) A
  ON STG.EMP_TYPE = A.EMP_TYPE
  GROUP BY STG.EMP_TYPE, DEPT, A.TOTAL_COUNT;
  </code></pre>
  
  <p>Is there any rewrite option or optimization strategy which can improve the query performance?</p>