title: simplifying query that has multiple WITH and multiple subqueries
body: <p>It is bothering me that for a simple query, I have to write out so many sub-selects and WITH statements. </p>
  
  <p>The question is: <strong>are there basic guidelines on how to simplify queries that have subqueries</strong>?</p>
  
  <p>Here's my query:</p>
  
  <pre><code>WITH cte_min
       AS (SELECT a.client_id,
                  a.specimen_source,
                  a.received_date
           FROM   f_accession_daily a
                  JOIN (SELECT DISTINCT f.client_id,
                                        f.received_date,
                                        f.accession_daily_key
                        FROM   F_ACCESSION_DAILY f
                               JOIN (SELECT CLIENT_ID,
                                            Min(received_date) MinRecDate
                                     FROM   F_ACCESSION_DAILY
                                     GROUP  BY CLIENT_ID) i
                                 ON f.CLIENT_ID = i.CLIENT_ID
                                    AND f.RECEIVED_DATE = i.MinRecDate) b
                    ON a.ACCESSION_DAILY_KEY = b.ACCESSION_DAILY_KEY),
       cte_max
       AS (SELECT a.client_id,
                  a.specimen_source,
                  a.received_date
           FROM   f_accession_daily a
                  JOIN (SELECT DISTINCT f.client_id,
                                        f.received_date,
                                        f.accession_daily_key
                        FROM   F_ACCESSION_DAILY f
                               JOIN (SELECT CLIENT_ID,
                                            Max(received_date) MaxRecDate
                                     FROM   F_ACCESSION_DAILY
                                     GROUP  BY CLIENT_ID) i
                                 ON f.CLIENT_ID = i.CLIENT_ID
                                    AND f.RECEIVED_DATE = i.MaxRecDate) b
                    ON a.ACCESSION_DAILY_KEY = b.ACCESSION_DAILY_KEY),
       cte_est
       AS (SELECT DISTINCT client_id,
                           MLIS_DATE_ESTABLISHED
           FROM   D_CLIENT
           WHERE  REC_ACTIVE_FLG = 1
                  AND MLIS_DATE_ESTABLISHED IS NOT NULL)
  SELECT DISTINCT f.client_id,
                  cmin.specimen_source,
                  cmin.received_date,
                  cmax.specimen_source,
                  cmax.received_date,
                  cest.MLIS_DATE_ESTABLISHED
  FROM   F_ACCESSION_DAILY f
         LEFT JOIN cte_max cmax
           ON cmax.CLIENT_ID = f.CLIENT_ID
         LEFT JOIN cte_min cmin
           ON cmin.CLIENT_ID = f.CLIENT_ID
         LEFT JOIN cte_est cest
           ON cest.CLIENT_ID = f.CLIENT_ID 
  </code></pre>
  
  <p>I am not asking necessarily for you to do the simplification yourself (although I would be very grateful for this), rather I am asking for general guidelines/directions on re-writing this query to be more elegant.</p>