Tell us if you have used the Common Table Expression (CTE) construction in your projects?

Common Table Expression (CTE) is a temporary result set that can be used within the main SQL query. In Go projects, where interaction with the database is frequent, CTE helps write more readable and structured queries.

For example, if you need to recursively obtain a hierarchy or break down a complex query into parts, CTE is very useful.

An example of using CTE in Go with the database/sql package:

query := `
WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date > NOW() - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;
`

rows, err := db.Query(query)
if err != nil {
    // handle error
}
// process rows

Thus, CTE helps make queries more understandable and maintainable.