You want to create a view active_customers that shows customers with at least one order in the last 30 days.
hard📝 Application Q15 of 15
SQL - Views
You want to create a view active_customers that shows customers with at least one order in the last 30 days. Given tables: customers(id, name) orders(id, customer_id, order_date) Which is the correct SQL to create this view?
ACREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days');
BCREATE VIEW active_customers AS SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE order_date > CURRENT_DATE - 30);
CCREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL 30 DAY;
DCREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days';
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
The view must include customers with orders in last 30 days only.
Step 2: Analyze each option's correctness
CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days'); uses EXISTS with correct date interval syntax and correlates orders to customers properly. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days'; uses JOIN but may duplicate customers if multiple orders exist. CREATE VIEW active_customers AS SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE order_date > CURRENT_DATE - 30); has incorrect date subtraction syntax. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL 30 DAY; uses LEFT JOIN but filters on order_date, which can exclude customers without recent orders incorrectly.
Final Answer:
CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days'); -> Option A
Quick Check:
Use EXISTS with correct date interval for filtering [OK]
Quick Trick:Use EXISTS with correct date interval for filtering [OK]
Common Mistakes:
MISTAKES
Using incorrect date interval syntax
Using JOIN causing duplicate rows
Filtering on LEFT JOIN columns incorrectly
Master "Views" in SQL
9 interactive learning modes - each teaches the same concept differently
