You want to create two CTEs: one named active_users selecting users with status = 'active', and another named user_orders selecting orders for those users. Which is the correct syntax?
hard📝 Application Q8 of 15
PostgreSQL - Common Table Expressions
You want to create two CTEs: one named active_users selecting users with status = 'active', and another named user_orders selecting orders for those users. Which is the correct syntax?
AWITH active_users = (SELECT * FROM users WHERE status = 'active'), user_orders = (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT * FROM user_orders;
BWITH active_users (SELECT * FROM users WHERE status = 'active'); user_orders (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)); SELECT * FROM user_orders;
CWITH active_users AS (SELECT * FROM users WHERE status = 'active'), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT * FROM user_orders;
DWITH active_users AS SELECT * FROM users WHERE status = 'active', user_orders AS SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users) SELECT * FROM user_orders;
Step-by-Step Solution
Solution:
Step 1: Recall multiple CTE syntax
Multiple CTEs are separated by commas, each defined as name AS (subquery).
Step 2: Analyze options
WITH active_users AS (SELECT * FROM users WHERE status = 'active'), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT * FROM user_orders; correctly uses AS and parentheses for both CTEs and separates them with a comma.
Final Answer:
WITH active_users AS (SELECT * FROM users WHERE status = 'active'), user_orders AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)) SELECT * FROM user_orders; -> Option C
Quick Check:
Multiple CTEs separated by commas with AS [OK]
Quick Trick:Separate multiple CTEs with commas and AS [OK]
Common Mistakes:
Using semicolons instead of commas between CTEs
Omitting AS keyword
Not enclosing subqueries in parentheses
Master "Common Table Expressions" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
