PostgreSQL - Views and Materialized Views
You want to create a view that shows total sales per customer but only for customers with sales over 1000. Which approach correctly uses a view to achieve this?
You want to create a view that shows total sales per customer but only for customers with sales over 1000. Which approach correctly uses a view to achieve this?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Use GROUP BY and HAVING to filter aggregated results
To get total sales per customer and filter by total, GROUP BY customer_id and HAVING SUM(amount) > 1000 is required.Step 2: Check each option's correctness
CREATE VIEW high_sales AS SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id HAVING SUM(amount) > 1000; correctly uses GROUP BY and HAVING; others misuse WHERE or omit GROUP BY.Final Answer:
CREATE VIEW high_sales AS SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id HAVING SUM(amount) > 1000; -> Option BQuick Check:
Use HAVING to filter aggregates in views = A [OK]
Quick Trick: Use HAVING to filter grouped data in views [OK]
Common Mistakes:
- Using WHERE instead of HAVING for aggregates
- Omitting GROUP BY with SUM
- Misplacing WHERE after GROUP BY
Master "Views and Materialized Views" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes