Databases · Middle · Question 28 of 75
What is a CTE (WITH ...) good for?
WITH recent AS (
SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;- 1Creating a permanent table
- 2Caching query results between requests
- ✓Correct answer: Naming a subquery to make complex queries readable, and enabling recursive queries
Explanation
Since PostgreSQL 12, simple CTEs are inlined into the main query and optimized together. MATERIALIZED forces computing the CTE once.