title: Combining two Teradata SQL queries
body: <p>I'm wanting to combine the following two queries into a single query. Here are the two queries. Anyone know if this is possible? The aggregation in the second query makes me think no, but I thought I'd throw it out there to the experts to see your thoughts.</p>
  
  <p>Query 1: </p>
  
  <pre><code>SELECT  a.id, a.ord_nb, a.scr,
          a.fr,
          a.rank,
          CASE WHEN tp.tps_jnr_cn &gt; 0 THEN 'Jnrs'
          WHEN tp.tps_chld_cn &gt; 0 THEN 'Chld'
          WHEN tp.tps_ifnt_cn &gt; 0 THEN 'Ifnt'
          WHEN tp.tps_snr_cn &gt; 0 THEN 'Snrs'
          ELSE 'Non'
          END AS Party_Seg,
          CASE WHEN gx.id IS NOT NULL THEN 1 ELSE 0 END 
          AS rpt
  FROM    xi.aff a
  LEFT JOIN oper.d_tps tp
      ON tp.tps_id = a.ord_nb
  LEFT JOIN XI.gst_exprnc gx
      ON a.id = gx.id
      AND tp.strt_dt &gt; gx.strt_dts + INTERVAL '30' DAY
      AND gx.exprnc_typ_id IN (309) --Park Experience
  QUALIFY Row_Number() Over (
  PARTITION BY ord_nb, scr, fr 
  ORDER BY rank DESC) = 1
  ORDER BY ord_nb, fr, scr
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>SELECT  a.ord_nb,
          a.fr, Max(a.rank), Min(a.rank),
          Max(a.rank) - Min(a.rank)
  FROM    (
      SELECT  ord_nb, fr, rank 
      FROM    xi.aff
      QUALIFY Row_Number() Over (
      PARTITION BY ord_nb, fr, scr
      ORDER BY rank DESC) = 1
          ) a
  GROUP BY 1, 2
  ORDER BY ord_nb, fr
  </code></pre>