Using Nested Queries Against CTE and Performance

Question

I used sql for a while and would like to know if it makes sense to convert a script containing a lot of CTEs into a regular nested script. I use:

WITH cte_account_pricelevelid 
     AS (SELECT a.accountid, 
                a.pricelevelid 
         FROM   companypricelist a 
                JOIN(SELECT accountid 
                     FROM   crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL) b 
                  ON a.accountid = b.accountid), 
     totals 
     AS (SELECT a.accountid, 
                a.pricelevelid 
         FROM   companypricelist a 
                JOIN(SELECT accountid 
                     FROM   crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL) b 
                  ON a.accountid = b.accountid), 
     totalsgrouped 
     AS (SELECT pricelevelid, 
                Count(*) counts 
         FROM   totals 
         GROUP  BY pricelevelid), 
     final 
     AS (SELECT cte.accountid, 
                cte.pricelevelid, 
                frequency.counts 
         FROM   cte_account_pricelevelid cte 
                CROSS JOIN totalsgrouped frequency 
         WHERE  cte.pricelevelid = frequency.pricelevelid), 
     mycolumns 
     AS (SELECT b.accountid, 
                b.pricelevelid, 
                b.counts 
         FROM   (SELECT accountid 
                 FROM   crm_accountbase 
                 WHERE  defaultpricelevelid IS NULL) a 
                JOIN final b 
                  ON a.accountid = b.accountid), 
     e 
     AS (SELECT *, 
                Row_number() 
                  OVER ( 
                    partition BY accountid 
                    ORDER BY counts, pricelevelid ) AS Recency 
         FROM   mycolumns), 
     cte_result 
     AS (SELECT accountid, 
                pricelevelid 
         FROM   e 
         WHERE  recency = 1) 

SELECT a.accountid, 
       a.defaultpricelevelid, 
       b.pricelevelid 
FROM   crm_accountbase a 
       JOIN cte_result b 
         ON a.accountid = b.accountid 

I feel it's silly to keep using the same query in my CTEs:

SELECT accountid 
                     FROM   crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL

But I do not know how to get around this. I believe that I can convert this, but I don’t know if there will be any performance gain.

select * from (select * from(select * from(...

Is it possible to significantly improve performance by turning this into nested SQL or simply simplifying CTE? If so, could you start me?

Answer 1

accountid ,

SELECT a.accountid, a.pricelevelid 
  FROM companypricelist a 
  JOIN (SELECT accountid 
          FROM crm_accountbase 
                     WHERE  defaultpricelevelid IS NULL) b 
    ON a.accountid = b.accountid

accountid
,

SELECT a.accountid, a.pricelevelid 
  FROM companypricelist a 
  JOIN crm_accountbase  b 
    ON a.accountid = b.accountid 
   AND b.defaultpricelevelid IS NULL

Answer 2

. ?

WITH 
 totals 
 AS (SELECT a.accountid, 
            a.pricelevelid 
     FROM   companypricelist a 
            JOIN(SELECT accountid 
                 FROM   crm_accountbase 
                 WHERE  defaultpricelevelid IS NULL) b 
              ON a.accountid = b.accountid), 
 totalsgrouped 
 AS (SELECT pricelevelid, 
            Count(*) counts 
     FROM   totals 
     GROUP  BY pricelevelid), 
 final 
 AS (SELECT cte.accountid, 
            cte.pricelevelid, 
            frequency.counts 
     FROM   totals cte 
            CROSS JOIN totalsgrouped frequency 
     WHERE  cte.pricelevelid = frequency.pricelevelid), 
 mycolumns 
 AS (SELECT b.accountid, 
            b.pricelevelid, 
            b.counts 
     FROM   final b 
          JOIN totals a
              ON a.accountid = b.accountid), 
 e 
 AS (SELECT *, 
            Row_number() 
              OVER ( 
                partition BY accountid 
                ORDER BY counts, pricelevelid ) AS Recency 
     FROM   mycolumns) 

SELECT a.accountid, 
       a.defaultpricelevelid, 
       b.pricelevelid 
FROM   crm_accountbase a 
       JOIN e b 
         ON a.accountid = b.accountid 
WHERE b.recency = 1