Keep your SQL sorted with Common Table Expression (CTE)

Ram Prasanna

2 min read

Hi guys, hope y’all doing good. Now that everything has started to become normal and the way how it was actually, there are still a few areas that are not sorted out and remain a mess.

Yes, I am talking about your SQL database. Do you have no idea how to maintain your data & make it more readable? Do not worry, I am here for the rescue!

In this blog, I am going to share some interesting thoughts on CTE (Common Table Expression) in SQL like how CTE helps in querying hierarchical data and keeps our query more readable

A common Table Expression is a temporary result set that exists for one query. CTE is defined/ initiated using the ‘WITH’ keyword followed by a name for the table expression and an actual base query called the anchor. 

cte_name AS (
  SELECT columns FROM table WHERE conditions

Let’s take a simple company and dealer table in which the company has many relations with dealers as an example. Table company holds the list of car companies and dealers with the list of dealer names and respective sales count.

Company Table
24 Tata
25 Toyota
28 Honda
30 Hyundai
Dealer Table
22124Dealer A24
23525Dealer B51
24928Dealer C41
25624Dealer A162
26325Dealer B155
27530Dealer D56

With the above setup, we will try to fetch a list of company names and dealers with the highest sales count accordingly.

In this scenario, ranking dealers with the highest sales count for a particular company would be our base result set, with that we can easily form our main query as shown below.

ranked_dealers AS (
    SELECT *, RANK( ) OVER (PARTITION BY cmpy_id ORDER BY sales_count desc) as rank
    FROM dealers 
SELECT AS company_name , AS dealer_name, rd.sales_count
FROM company AS cmpy
LEFT JOIN ranked_dealers AS rd ON = rd.cmpy_id
WHERE rd.rank = 1;
Query Result
TataDealer A162
ToyotaDealer B155
HondaDealer C41
HyundaiDealer D56

Now we have the best dealers for the corresponding companies. By this simple example, we get a basic understanding of how CTE works. The same can be achieved using a subquery.

But for complex queries, we can have multiple table expressions with different names to use in the main query which helps improve readability and easy debugging.


CTE WITH clause runs once and generates a temporary copy table, this is referred to as MATERIALIZE. It helps in query performance. However, this comes with disadvantages such as all columns are computed even if they are not used in the main query and mainly indexes won’t work well when used with WHERE or JOIN clauses.

Prior to POSTGRES 12, all CTEs are materialized. By default, Later versions materialize only if CTE is referenced more than once, and use inline if referenced only once which has a huge impact on query execution time. We can change the default behavior via code as shown below.



Using CTE with Rails

CTE can be implemented on Rails using the ActiveRecordExtended gem. The above query can be translated into ActiveRecord as,

Company.with( ranked_dealers: “ SELECT *, RANK( ) OVER ( PARTITION BY cmpy_id ORDER BY sales_count desc ) as rank FROM dealers ” )
       .joins( “ LEFT JOIN ranked_dealers ON  ranked_dealers.cmpy_id = ” )
       .where( “ ranked_dealers.rank = 1 ” )
       .select( “ AS company_name , AS dealer_name, ranked_dealers.sales_count” )

Does this blog on CTE sound captivating!!! Hope you have got an idea of how to have your data sorted out. Do let me know your thoughts in the comments.

Let us all soon meet in my next blog. Until then, it’s a wrap!

Related posts:

Leave a Reply

Your email address will not be published. Required fields are marked *