# The best Postgres feature you're not using - CTEs aka WITH clauses

DevFeed: [The best Postgres feature you're not using - CTEs aka WITH clauses](<https://devfeed.tech/articles/the-best-postgres-feature-you-re-not-using-ctes-aka-with-clauses-41154.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/>)

Author: Map

Published: 2013-11-18T20:55:56Z

Content type: article

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [array-agg](<https://devfeed.tech/tags/array-agg.md>), [cte](<https://devfeed.tech/tags/cte.md>), [feature](<https://devfeed.tech/tags/feature.md>), [join](<https://devfeed.tech/tags/join.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [readability](<https://devfeed.tech/tags/readability.md>), [sql](<https://devfeed.tech/tags/sql.md>)

## AI overview

This article explains how PostgreSQL common table expressions (CTEs), also called WITH clauses, make SQL queries more readable and composable. It demonstrates chaining CTEs to analyze tasks assigned to users and identify users responsible for more than half of a project's tasks, while noting that readability may involve a performance tradeoff.

## Source excerpt

SQL by default isn't typically friendly to dive into, and especially so if you're reading someone else's already created queries. For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. I was recently reminded of a key feature in Postgres that most don't use by @timonk highlighting it in his AWS Re:Invent Redshift talk. The simple feature actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be. The feature itself is known as CTEs or common table expressions, you may also here it referred to as WITH clauses. The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. You can create multiple of these which then allow for clear building blocks and make it simple to follow what you're doing. Lets take a look at a nice simple one: WITH users_tasks AS ( SELECT users.email, array_agg(tasks.name) as task_list, projects.title FROM users, tasks, project WHERE users.id = tasks.user_id projects.title = tasks.project_id GROUP BY users.email, projects.title ) Using this I could now just append some basic other query on to the end that references this CTE users_tasks. Something akin to: SELECT * FROM users_tasks; But where it becomes more interesting is chaining these together. So while I have all tasks assigned to each user here, perhaps I want to then find which users are responsible for more than 50% of the tasks on a given project, thus being the bottleneck. To oversimplify this we could do it a couple of ways, total up the tasks for each project, and then total up the tasks for each user per project: total_tasks_per_project AS ( SELECT project_id, count(*) as task_count FROM tasks GROUP BY project_id ), tasks_per_project_per_user AS ( SELECT user_id, project_id, count(*) as task_count FROM tasks GROUP BY user_id,