PostgreSQL - Views and Materialized Views
You want to create a materialized view that stores the top 5 selling products by quantity. Which SQL snippet correctly creates this materialized view?
You want to create a materialized view that stores the top 5 selling products by quantity. Which SQL snippet correctly creates this materialized view?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Identify aggregation and ordering
We need to sum quantity per product and order by total descending, limiting to 5 rows.Step 2: Analyze options
CREATE MATERIALIZED VIEW top_products AS SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id ORDER BY total_qty DESC LIMIT 5; correctly groups, sums, orders, and limits. Others miss grouping, ordering, or limit.Final Answer:
CREATE MATERIALIZED VIEW top_products AS SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id ORDER BY total_qty DESC LIMIT 5; -> Option DQuick Check:
Top 5 products = group, sum, order desc, limit 5 [OK]
Quick Trick: Use GROUP BY, ORDER BY DESC, and LIMIT for top N queries [OK]
Common Mistakes:
- Omitting GROUP BY when using SUM
- Not ordering by aggregated column
- Missing LIMIT clause
Master "Views and Materialized Views" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes