Simplify complex queries with CTEs

💻 Tech

In PostgreSQL, WITH clause with AS keyword is used to create a CTE or Common Table Expression. CTEs are helpful in creating temporary tables to make the queries more readable and to simplify complex queries.

WITH trx AS (
  SELECT COUNT(*), type FROM ledger GROUP BY type HAVING COUNT(*) > 1
)
SELECT * FROM transactions WHERE type IN (SELECT type FROM trx);