TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 25

Views and Materialized Views: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1What is a VIEW (a stored SELECT, run live on each query)?
  2. Q2What is a MATERIALIZED VIEW (a stored result set, refreshed on demand)?
  3. Q3Why share tested logic as a view rather than copy-pasting SQL?
  4. Q4View vs MV: which trades freshness for query speed, and why?
  5. Q5What does CREATE OR REPLACE VIEW do (when allowed)?
  6. Q6Why are views great for the "metrics layer" of a stack?
  7. Q7What does CREATE MATERIALIZED VIEW ... WITH NO DATA do?
  8. Q8What does REFRESH MATERIALIZED VIEW do?
  9. Q9What does REFRESH MATERIALIZED VIEW CONCURRENTLY require (unique index)?
  10. Q10Why does a concurrent refresh need a unique index on the MV?
  11. Q11Layered stack: raw -> cleaned -> metrics - what lives where?
  12. Q12What is a "raw" view (passthrough + minimal column selection)?
  13. Q13What is a "cleaned" view (typed, trimmed, deduped values)?
  14. Q14What is a "metrics" view (aggregates, KPIs)?
  15. Q15Why are views read-only abstractions for downstream consumers?
  16. Q16What does DROP VIEW ... CASCADE do (and why is it risky)?
  17. Q17How do you list dependencies on a view before dropping (concept)?
  18. Q18Why does a view inherit security from underlying tables?
  19. Q19What is the difference between CREATE VIEW and CREATE TEMP VIEW?
  20. Q20Why an MV makes sense for slow analytics that don't need real-time freshness?
  21. Q21What's the freshness model of a normal view (always current) vs MV (refreshed)?
  22. Q22Why is a daily refresh schedule common for MVs (and when is hourly needed)?
  23. Q23Can you index a materialized view? (Yes - Topic 20 indexing applies).
  24. Q24Why is a view a good place to enforce join discipline (one canonical join)?
  25. Q25Name two analyst reports you'd ship as a view vs as an MV.

PLAIN VIEWS

  1. Q26Create v_delivered_orders: orders WHERE order_status = 'Delivered'.
  2. Q27Create v_customer_full_name: customer_id + (first_name||' '||last_name).
  3. Q28Create v_orders_enriched: orders joined to customers and stores (key columns).
  4. Q29Create v_orders_clean: filter Failed/Cancelled out + standardize status case.
  5. Q30SELECT through v_delivered_orders and count rows.
  6. Q31Create v_high_value_orders: net_total > 5000.
  7. Q32Create v_gold_platinum_customers: tier IN ('Gold','Platinum').
  8. Q33Create v_products_with_brand: products + brand + category names.
  9. Q34Create v_returns_with_customer: returns -> orders -> customers.
  10. Q35Create v_revenue_by_region: aggregated SUM(net_total) per region.
  11. Q36Create v_orders_2025: just orders from 2025 (date_trunc, Topic 23).
  12. Q37Drop v_high_value_orders.
  13. Q38Create or replace v_orders_clean to also exclude Returned.
  14. Q39Create v_employee_with_dept: employees + department name.
  15. Q40Create v_top_customers: top 100 customers by lifetime spend.
  16. Q41Create v_orders_with_items_count: orders + count(items).
  17. Q42Create v_active_stores: stores with at least one order.
  18. Q43Create v_customer_latest_order using DISTINCT ON (Topic 22).
  19. Q44Create v_monthly_revenue using date_trunc('month') (Topic 23).
  20. Q45SELECT a slice from v_monthly_revenue for one year.
  21. Q46Create v_tickets_open: tickets not yet resolved.
  22. Q47Create v_pay_slip_clean: parse month-year text into a real date (Topic 23).
  23. Q48Create v_reviews_clean: trim text + drop NULL ratings.
  24. Q49List the row count of every view you've created so far.
  25. Q50Drop a view safely after checking no other view depends on it.

MATERIALIZED VIEWS

  1. Q51Create MV mv_monthly_revenue: month, total revenue, order count.
  2. Q52REFRESH MATERIALIZED VIEW mv_monthly_revenue.
  3. Q53Create UNIQUE INDEX on mv_monthly_revenue(month) (prereq for CONCURRENTLY).
  4. Q54REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue.
  5. Q55Create mv_revenue_by_region with a unique key + index.
  6. Q56Create mv_customer_lifetime: per customer total orders, total revenue, latest order.
  7. Q57Add a UNIQUE INDEX on mv_customer_lifetime(customer_id).
  8. Q58Create mv_store_kpis: per store monthly revenue & order count.
  9. Q59Create mv_product_sales: per product units sold + revenue.
  10. Q60Create mv_orders_with_items_count (heavy join - good MV candidate).
  11. Q61Create mv_top_customers_by_year: top 100 per year.
  12. Q62Create mv_returns_by_category.
  13. Q63Create mv_calls_hourly: per hour-of-day call count & median duration (Topic 22/23).
  14. Q64Create mv_page_views_hourly per device.
  15. Q65Create mv_employee_salary_latest: latest salary per employee (DISTINCT ON, Topic 22).
  16. Q66Create mv_inventory_latest: latest snapshot per (warehouse, prod).
  17. Q67Add an index on mv_inventory_latest(warehouse_id, prod_id) (Topic 20).
  18. Q68REFRESH the MV and re-count rows.
  19. Q69Create MV ... WITH NO DATA; then REFRESH to populate.
  20. Q70Drop an MV you created.
  21. Q71Create mv_revenue_quarterly with a unique (region, quarter) key.
  22. Q72Create mv_ticket_sla: per priority median + P95 resolution hours (Topic 22).
  23. Q73Create mv_daily_revenue (gap-filled spine, Topic 23) + UNIQUE INDEX(d).
  24. Q74Compare row counts: live aggregate query vs MV (should match post-REFRESH).
  25. Q75EXPLAIN ANALYZE the MV scan (Topic 19) - note index-only scan if applicable.

LAYERED STACK & MAINTENANCE

  1. Q76Layer 1 (raw): v_orders_raw = passthrough of sales.orders with renamed cols.
  2. Q77Layer 2 (cleaned): v_orders_clean built on v_orders_raw (filter + trim).
  3. Q78Layer 3 (metrics): v_monthly_revenue_kpis built on v_orders_clean.
  4. Q79Show that v_monthly_revenue_kpis transitively depends on v_orders_raw.
  5. Q80Materialize the metrics layer: mv_monthly_revenue_kpis.
  6. Q81Replace the metrics view with an MV; consumers keep the same name (drop + create).
  7. Q82Stack: raw -> cleaned -> metrics for returns; one MV at the top.
  8. Q83Stack: raw -> cleaned -> metrics for tickets (SLA dashboard).
  9. Q84Decision: which of your 3 layers should be an MV (heaviest aggregate)?
  10. Q85REFRESH order: refresh bottom-up vs top-down - discuss.
  11. Q86Detect dependent views before dropping a base view (pg_depend/pg_views).
  12. Q87DROP a view CASCADE and explain what got dropped (then recreate).
  13. Q88Schedule strategy: daily vs hourly refresh - pick for monthly_revenue_kpis.
  14. Q89Add a UNIQUE INDEX on each MV that needs CONCURRENTLY.
  15. Q90Refresh strategy: stale-MV note vs CONCURRENT - tradeoffs.
  16. Q91Build a "customer-360" view (Topic 24 spirit, relational) on top of the stack.
  17. Q92Time-series MV (Topic 23): daily revenue + 7-day MA, with index.
  18. Q93Percentile MV (Topic 22): median & P95 net_total per region.
  19. Q94Pivot MV (Topic 21): region x month revenue.
  20. Q95Build an MV for the executive KPI strip (orders, median, P95, latest).
  21. Q96Document each MV's refresh cadence in a comment header.
  22. Q97Test view substitutability: same query on view vs base - equal rows?
  23. Q98Drop all the MVs you created (cleanup) - list first, then drop.
  24. Q99Drop all the views you created (in dependency-safe order).
  25. Q100Recreate the full 3-layer stack as a script you can re-run.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Layered stack discipline: what belongs in raw / cleaned / metrics and why.
  2. Q2When does a view enable an index-only scan on the base table?
  3. Q3View inlining: how the planner expands views during optimization.
  4. Q4Why CREATE OR REPLACE VIEW requires the same column list shape.
  5. Q5Updatable vs non-updatable views in Postgres (concept).
  6. Q6WITH CHECK OPTION semantics (CASCADED vs LOCAL) - concept.
  7. Q7View vs MV vs summary table - three-way decision matrix.
  8. Q8Why MV CONCURRENTLY requires a UNIQUE INDEX (no full lock).
  9. Q9Indexing strategy for MVs (Topic 20): primary lookup index + secondary.
  10. Q10Refresh ordering for a stack of MVs (dependencies).
  11. Q11Detect view dependencies via pg_depend / pg_views (concept).
  12. Q12DROP CASCADE trap: how to inventory dependents first.
  13. Q13Schema namespacing for views (e.g. analytics.v_*) - why it matters.
  14. Q14View security & search_path pitfalls (concept).
  15. Q15When inlining hurts the plan (many small views combined).
  16. Q16Materializing a percentile MV (Topic 22) - refresh cost vs query cost.
  17. Q17Time-series MVs (Topic 23): daily vs hourly grain trade-offs.
  18. Q18Pivot MV (Topic 21) shape vs long MV - when which is right.
  19. Q19EXPLAIN ANALYZE on MV vs underlying view (Topic 19) - what to look for.
  20. Q20Stale-data tolerance per consumer - driving refresh cadence.
  21. Q21Combining views from many schemas - naming + ownership.
  22. Q22Documenting an MV's refresh contract in a comment.
  23. Q23When a view becomes a performance crutch (anti-pattern).
  24. Q24Versioning views (v2 alongside v1) for safe migration.
  25. Q25Anti-patterns: views over views over views (depth that hurts).

LAYERED STACKS

  1. Q26Build the orders stack: v_orders_raw -> v_orders_clean -> v_orders_enriched.
  2. Q27v_orders_clean: trim status, drop Failed, normalize date typing.
  3. Q28v_orders_enriched: join customers, stores, region, payment_mode.
  4. Q29v_revenue_by_region_month built on the enriched view.
  5. Q30Returns stack: v_returns_raw -> v_returns_clean -> v_returns_by_category.
  6. Q31Tickets stack with SLA: v_tickets_clean -> v_ticket_sla (median/P95).
  7. Q32Customers stack: v_customers_clean (full_name, primary city via addresses).
  8. Q33Employees stack: v_employees_clean + v_employee_with_dept.
  9. Q34Products stack: v_products_clean + v_products_with_brand_category.
  10. Q35Build v_inventory_latest on top of supply_chain.inventory_snapshots (Topic 22).
  11. Q36Build v_customer_latest_order (DISTINCT ON) and v_customer_first_order.
  12. Q37Build v_orders_with_basket (items joined + count).
  13. Q38Build v_orders_with_payment (latest payment, Topic 22).
  14. Q39Build v_revenue_by_region quarter (Topic 23) on top of the enriched orders.
  15. Q40Stack a percentiles layer: v_region_aov_percentiles (Topic 22).
  16. Q41Stack a window-rank layer: v_top_customers_per_region (Topic 16).
  17. Q42Stack a time-series gap-filled layer (Topic 23) v_daily_revenue.
  18. Q43Stack a pivot layer (Topic 21): v_region_x_month_revenue.
  19. Q44Build v_customer_360 as a layered view (Topic 24 spirit, relational).
  20. Q45Cross-stack: v_orders_clean + v_customers_clean -> v_orders_with_customer.
  21. Q46Show that v_revenue_by_region depends transitively on v_orders_raw.
  22. Q47Rebuild a view with CREATE OR REPLACE; assert column list unchanged.
  23. Q48Add a new column to the cleaned layer; propagate through the stack.
  24. Q49Drop a leaf view safely (verify no dependents first).
  25. Q50Write a "recreate-stack" script that runs idempotently.

MV MAINTENANCE & INDEXING

  1. Q51Convert v_monthly_revenue -> MV; add UNIQUE INDEX(month); REFRESH CONCURRENTLY.
  2. Q52mv_revenue_by_region (region, month) + UNIQUE INDEX; concurrent refresh.
  3. Q53mv_customer_lifetime (customer_id PK) + indexes for tier / region.
  4. Q54mv_store_kpis monthly per store + UNIQUE INDEX(store_id, month).
  5. Q55mv_product_sales by product + secondary index on category.
  6. Q56mv_top_customers_per_region (Topic 16) + index on (region, rank).
  7. Q57mv_ticket_sla per priority median+P95 (Topic 22) + UNIQUE INDEX(priority).
  8. Q58mv_calls_hourly (hour-of-day, Topic 23) + UNIQUE INDEX(hour).
  9. Q59mv_inventory_latest (warehouse_id, prod_id) + UNIQUE INDEX.
  10. Q60mv_daily_revenue (date, Topic 23) gap-filled + UNIQUE INDEX(d).
  11. Q61mv_revenue_quarterly (region, quarter) + UNIQUE INDEX.
  12. Q62mv_returns_by_category (category) + UNIQUE INDEX.
  13. Q63mv_pay_slip_monthly (employee_id, period) + UNIQUE INDEX.
  14. Q64mv_employee_salary_latest (employee_id) + UNIQUE INDEX.
  15. Q65mv_orders_clean (heavy join) + secondary indexes for common filters (Topic 20).
  16. Q66mv_page_view_hourly (hour, device) + UNIQUE INDEX.
  17. Q67mv_region_x_month_revenue (Topic 21 pivot) + UNIQUE INDEX(region).
  18. Q68mv_top_customers_year (year, rank) + UNIQUE INDEX.
  19. Q69mv_customer_360_summary (customer_id) + UNIQUE INDEX (Topic 24-style).
  20. Q70Refresh dependency order: list which MVs must refresh before others.
  21. Q71Write a refresh script that runs in dependency order.
  22. Q72CONCURRENTLY-safe refresh requirements: verify each MV has a UNIQUE INDEX.
  23. Q73Compare REFRESH (locks) vs REFRESH CONCURRENTLY (online) - pick per MV.
  24. Q74EXPLAIN ANALYZE a query against the MV (Topic 19) - index-only?
  25. Q75Decide hourly vs daily refresh per MV; document in a comment.

COMPOSED ANALYTICS VIEWS

  1. Q76v_region_dashboard: orders, revenue, median AOV, P95 AOV (Topic 22).
  2. Q77mv_region_dashboard + UNIQUE INDEX(region); concurrent refresh.
  3. Q78v_cohort_retention (Topic 23 spine) layered.
  4. Q79v_funnel_stage_counts (web_events) layered.
  5. Q80v_delivery_sla per region (median/P95, Topic 22).
  6. Q81v_pricing_corridor per product (P25-P75, Topic 22) + latest price (DISTINCT ON).
  7. Q82v_customer_rfm (NTILE, Topic 16) - base layer for Topic 27 preview.
  8. Q83v_anomaly_daily (Topic 22 P95 / Topic 23 spine) on top of mv_daily_revenue.
  9. Q84v_top_products_per_category (Topic 16) -> mv with refresh.
  10. Q85v_heatmap_weekday_hour (Topic 23) -> mv (small but indexed).
  11. Q86v_audit_change_summary on record_changes.
  12. Q87v_customer_360_relational with arrays-of-IDs (Topic 24-adjacent).
  13. Q88v_pay_slip_summary per dept (median/P95 net, Topic 22).
  14. Q89v_inventory_value per warehouse (latest snap x cost_price).
  15. Q90v_revenue_decomposition new/returning per month.
  16. Q91v_orders_with_filter_pivot (Topic 21) of status counts.
  17. Q92v_region_x_quarter median AOV (Topic 22+21).
  18. Q93v_dynamic_sla per region (target = prior-Q P90, Topic 22).
  19. Q94mv_customer_360_summary (Topic 24-spirit) refreshed nightly.
  20. Q95v_growth_metrics MoM / YoY per region (Topic 23).
  21. Q96mv_growth_metrics + UNIQUE INDEX(region, month); test refresh.
  22. Q97Document each MV's refresh cadence and dependency chain.
  23. Q98Validate substitutability: live aggregate vs MV - totals match?
  24. Q99Drop all created MVs in dependency-safe order.
  25. Q100Drop the full stack; provide an idempotent recreate script.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Designing a 3-layer view stack at scale: raw / cleaned / metrics responsibilities.
  2. Q2View inlining limits: when the planner can't push predicates through.
  3. Q3Materializing for query speed vs view for freshness - a decision rubric.
  4. Q4REFRESH MV CONCURRENTLY constraints + unique-index requirement at scale.
  5. Q5Refresh choreography for a DAG of MVs (dependency-safe ordering).
  6. Q6Indexing MVs (Topic 20) - primary unique + secondary range + expression.
  7. Q7Plan inspection on MV-backed dashboards (Topic 19) - index-only-scan goals.
  8. Q8DROP CASCADE blast-radius mitigation (dependency audit).
  9. Q9Backward-compat view evolution (versioned vN; deprecation pattern).
  10. Q10Stale-data SLAs per consumer and how to drive refresh cadence.
  11. Q11Hybrid: live view on top of an MV (last-N-minutes UNIONed in).
  12. Q12Multi-grain MVs (day / week / month) with consistent rollups.
  13. Q13Partial MV (filtered set) for hot dashboards.
  14. Q14View security & search_path discipline at scale.
  15. Q15Anti-pattern: deeply nested views causing replanning cost.
  16. Q16Time-series MV refresh strategy (Topic 23) - incremental concept.
  17. Q17Percentile MV (Topic 22) - chunked rebuild for huge tables.
  18. Q18JSON-export MV (Topic 24) - when to ship API payload as an MV.
  19. Q19Pivot MV (Topic 21) - wide vs long, schema stability concerns.
  20. Q20Validation checks against MVs (totals match base) as a contract.
  21. Q21Documentation: per MV - owner, grain, refresh cadence, dependents.
  22. Q22View naming & namespacing (raw_/clean_/m_/mv_) - stack hygiene.
  23. Q23Index maintenance cost on MV refresh - when to drop+recreate.
  24. Q24Multi-tenant view layering (per-region filtered views).
  25. Q25When NOT to materialize (small / cheap / volatile data).

PRODUCTION VIEW STACKS

  1. Q26Orders stack: raw -> clean -> enriched -> metrics (4 views) with proper dependencies.
  2. Q27Customers stack: raw -> clean (full_name, primary city) -> enriched (latest order Topic 22).
  3. Q28Products stack: raw -> clean -> enriched (brand+category+promo flag).
  4. Q29Inventory stack: raw -> latest-per-(warehouse,prod) (Topic 22) -> value (x cost_price).
  5. Q30Tickets stack: raw -> clean -> SLA per priority (median/P95, Topic 22).
  6. Q31Pay-slip stack: raw -> clean (period date) -> summary per dept (Topic 22).
  7. Q32Returns stack: raw -> enriched (with order/customer) -> metrics by category.
  8. Q33Audit stack: record_changes raw -> typed -> change-summary.
  9. Q34Web stack: page_views raw -> cleaned -> hourly (Topic 23).
  10. Q35Calls stack: raw -> enriched (agent dept) -> SLA / hour-of-day (Topic 22+23).
  11. Q36Cross-stack: orders + customers -> v_orders_with_customer (canonical join).
  12. Q37Cross-stack: orders + payments (latest, Topic 22) -> v_orders_with_payment.
  13. Q38Cross-stack: orders + shipments + delivery_days (Topic 23).
  14. Q39Cross-stack: returns -> orders -> customers -> tier-aware metrics.
  15. Q40Region rollup stack: stores -> region revenue/quarter (Topic 21+23).
  16. Q41Region percentiles layer: median/P90/P95 AOV per region (Topic 22) on top of metrics.
  17. Q42Top-N layer (Topic 16): top customers per region by lifetime spend.
  18. Q43Cohort layer (Topic 23 + window): cohort_month + period_index 0..11.
  19. Q44RFM layer (Topic 16 NTILE): recency/freq/monetary scores per customer.
  20. Q45Customer-360 layer (Topic 24-spirit): joined arrays-of-IDs of latest activity.
  21. Q46Anomaly layer (Topic 22+23): daily revenue beyond P95 flagged.
  22. Q47Funnel layer: stage counts from web_events + drop-off ratios.
  23. Q48Pricing-corridor layer (Topic 22): P25-P75 + latest selling price.
  24. Q49Sales-trend layer: MoM / YoY per region (Topic 23 + window).
  25. Q50Build an "exec metrics" leaf view that pulls from all the above.

MV REFRESH CHOREOGRAPHY

  1. Q51Convert the orders-metrics layer to MV + UNIQUE INDEX + CONCURRENTLY-safe.
  2. Q52mv_revenue_by_region_month (region, month) with UNIQUE + range index.
  3. Q53mv_customer_lifetime + secondary indexes (tier, region).
  4. Q54mv_store_kpis monthly per store; refresh cadence comment in DDL.
  5. Q55mv_ticket_sla per priority; UNIQUE INDEX + plan-check (Topic 19).
  6. Q56mv_daily_revenue (Topic 23) gap-filled + UNIQUE INDEX(d) + plan-check.
  7. Q57mv_inventory_latest + UNIQUE INDEX(warehouse_id, prod_id).
  8. Q58mv_top_customers_per_region (Topic 16) + (region, rank) UNIQUE.
  9. Q59mv_region_percentiles AOV (Topic 22) + UNIQUE INDEX(region).
  10. Q60mv_region_x_month_revenue (Topic 21 pivot) + UNIQUE INDEX(region).
  11. Q61mv_funnel_counts + UNIQUE INDEX(stage).
  12. Q62mv_cohort_retention (cohort_month, period_index) + UNIQUE INDEX.
  13. Q63mv_rfm_scores (customer_id) + UNIQUE INDEX.
  14. Q64mv_customer_360_summary (customer_id) + UNIQUE INDEX (Topic 24-spirit).
  15. Q65Dependency graph: list refresh order for the above MVs.
  16. Q66Script: refresh all MVs in dependency order with CONCURRENTLY where possible.
  17. Q67Detect MVs missing UNIQUE INDEX (would block CONCURRENTLY) - audit query.
  18. Q68Compare REFRESH (lock) vs REFRESH CONCURRENTLY timings - concept + measurement.
  19. Q69Hybrid view: mv_daily_revenue UNION live last-N-hour aggregate.
  20. Q70Partial MV: only the last 90 days (Topic 23 cutoff) - speed up refresh.
  21. Q71Multi-grain MVs (day/week/month) revenue with reconciliation checks.
  22. Q72Validation: per MV - totals match the live aggregate (assertion query).
  23. Q73Drop+recreate index on MV refresh to keep bloat low (concept).
  24. Q74Refresh budgeting: which MVs fit a 5-minute SLO; which need hourly.
  25. Q75Document each MV: owner, grain, refresh, dependents - in DDL comment.

COMPOSED ANALYTICS MVS

  1. Q76mv_region_dashboard (orders, median/P90/P95 AOV, latest order, rank) - exec strip.
  2. Q77mv_cohort_triangle_with_ltv: cohort x period retention + cumulative revenue (Topic 23).
  3. Q78mv_delivery_sla per region (median/P95 days + breach %, Topic 22).
  4. Q79mv_ticket_sla_extended per priority/month (Topic 23).
  5. Q80mv_pricing_corridor per product + latest price + drift flag (Topic 22).
  6. Q81mv_inventory_value per warehouse + days-of-cover (Topic 23).
  7. Q82mv_anomaly_daily revenue beyond regional P95 (Topic 22+23) for alerting.
  8. Q83mv_funnel_drop_off per stage + per hour (Topic 23).
  9. Q84mv_customer_rfm_segments (Topic 16 NTILE) - Topic 27 preview.
  10. Q85mv_audit_change_jumps > 50% price-change (Topic 26 preview).
  11. Q86mv_heatmap_weekday_hour (Topic 23) - small but indexed for UI.
  12. Q87mv_region_x_quarter median AOV (Topic 22+21+23).
  13. Q88mv_growth_metrics MoM/YoY per region (Topic 23).
  14. Q89mv_customer_360_summary (Topic 24-spirit) - refreshed nightly, comment cadence.
  15. Q90mv_pay_slip_dept_summary (median/P95 net by dept, Topic 22).
  16. Q91mv_top_products_per_category (Topic 16) - for catalog highlights.
  17. Q92mv_revenue_decomposition new/returning per month per region.
  18. Q93mv_inventory_stockout_streaks per warehouse (Topic 23 + gap-and-island concept).
  19. Q94mv_dynamic_sla_attainment (target = prior-Q P90, Topic 22).
  20. Q95mv_calls_hourly per agent + median duration (Topic 22+23).
  21. Q96mv_returns_root_cause per category/region (counts + refund %).
  22. Q97mv_executive_strip (orders, median, P95, SLA%, churn-risk count, rank).
  23. Q98Validation suite: assert mv totals = live totals for all the above.
  24. Q99Drop-all script in dependency-safe order (audit dependencies first).
  25. Q100Capstone: deliver a 3-layer stack (raw / cleaned / metrics) + MV at the top, with UNIQUE INDEX, refresh cadence documented, plan-checked (Topic 19), and reconciliation.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Designing the semantic / metrics layer (raw -> core -> marts -> exec).
  2. Q2View vs MV vs summary table - a decision rubric per workload.
  3. Q3DAG-driven refresh orchestration; idempotency and ordering guarantees.
  4. Q4Versioned view rollout (v1 -> v2) without breaking consumers.
  5. Q5Partial MVs (filtered hot window) + hybrid live-tail UNION.
  6. Q6Multi-grain MV rollups (day/week/month) - consistency proofs.
  7. Q7Incremental MV refresh - concept and constraints in Postgres.
  8. Q8CONCURRENTLY mechanics + unique-index requirement at scale.
  9. Q9Plan inspection for MV-backed dashboards (Topic 19); index-only goals.
  10. Q10Index strategy on MVs (Topic 20): unique + range + expression + covering.
  11. Q11Cascading drops: dependency audit + safe rollback strategy.
  12. Q12SLA-driven refresh cadence: 5-min / hourly / daily tiers.
  13. Q13Cost model for refresh under load; window selection.
  14. Q14View aliasing & deprecation period; consumer migration plan.
  15. Q15JSON-export MVs (Topic 24) as API products; envelope contract.
  16. Q16Multi-tenant filtered views; row-level isolation patterns.
  17. Q17Latency-SLO MVs (P95, Topic 22) and burn-rate alerting.
  18. Q18Time-series MV layout (Topic 23) and partition-of-grain.
  19. Q19Schema evolution under MVs: drop-and-recreate vs in-place.
  20. Q20Validation contracts: per MV totals match base; alarms on drift.
  21. Q21Documentation as data: store owner / grain / cadence in MV comment.
  22. Q22View-stack hygiene: depth limits, naming, ownership.
  23. Q23Building the "exec strip" as a single MV from many marts.
  24. Q24Topic 26 dedup integration: cleaned layer hosts dedup logic (preview).
  25. Q25Topic 27 cohort / RFM: marts vs MVs - refresh cadence implications.

SEMANTIC-LAYER ARCHITECTURES

  1. Q26Raw layer (passthrough + renames): v_raw_orders/customers/products/stores/regions.
  2. Q27Core layer (cleaned, typed, deduped): v_core_orders/customers/products.
  3. Q28Mart layer: v_mart_revenue_by_region_month, v_mart_customer_lifetime.
  4. Q29Exec layer: v_exec_region_strip (orders, median/P90/P95 AOV, latest).
  5. Q30Cross-domain customer mart: orders + reviews + tickets + payments latest (Topic 22).
  6. Q31Product mart: brand + category + promo + margin + latest price (Topic 22).
  7. Q32Inventory mart: latest snapshot per SKU + value + days-of-cover (Topic 22+23).
  8. Q33Web-events mart: sessions + funnel + heatmap (Topic 23).
  9. Q34SLA mart: delivery + ticket + call SLA per priority/region (Topic 22).
  10. Q35Pricing mart: corridor (P25-P75) + drift + latest selling price.
  11. Q36Cohort mart: cohort x period retention + cumulative LTV (Topic 23, Topic 27 preview).
  12. Q37RFM mart: NTILE recency/freq/monetary per customer (Topic 16, Topic 27 preview).
  13. Q38Audit mart: change history + > 50% jumps + actor (Topic 26 preview).
  14. Q39Region rollup mart: stores aggregated to region with percentiles.
  15. Q40Multi-grain rollup mart: day -> week -> month with reconciliation.
  16. Q41Anomaly mart: daily P95/P99 flags (Topic 22+23).
  17. Q42JSON-export mart (Topic 24): per region exec strip as nested doc.
  18. Q43Time-series mart (Topic 23): date -> rev + MA + MoM/YoY.
  19. Q44Funnel mart per channel/hour (Topic 23).
  20. Q45Pricing-corridor JSON mart (Topic 24) for the catalog API.
  21. Q46Customer-360 relational mart (Topic 24-adjacent) - IDs of latest activity.
  22. Q47Lifecycle-stage mart per customer per month.
  23. Q48Reactivation mart: dormancy -> reactivation per month.
  24. Q49Heatmap mart weekday x hour per device (Topic 23).
  25. Q50Compose the full semantic layer as a runnable script.

REFRESH ORCHESTRATION & MV PIPELINES

  1. Q51Build the refresh DAG (dependency edges) for every MV created so far.
  2. Q52Generate a refresh script that runs MVs in topological order.
  3. Q53Mark each MV CONCURRENTLY-eligible (UNIQUE INDEX present)? Audit query.
  4. Q54Hybrid MV + live-tail: mv_daily_revenue UNION live last-N-hour aggregate.
  5. Q55Partial MV: only last 90 days of mv_daily_revenue (Topic 23 cutoff).
  6. Q56Chunked refresh design for huge percentile MVs (Topic 22).
  7. Q57Multi-grain MV trio (day/week/month) revenue + reconciliation assert.
  8. Q58Time-budget refresh planner - group MVs by cadence tier.
  9. Q59Idempotent rebuild for a date range - DROP + CREATE + REFRESH dance.
  10. Q60Detect dependency cycles in the view DAG (audit, concept).
  11. Q61Versioned MV rollout v2 alongside v1; deprecation window.
  12. Q62Plan inspection (Topic 19) for every consumer query - index-only check.
  13. Q63Index strategy on each MV (Topic 20): unique + covering + expression.
  14. Q64Drop-and-recreate vs ALTER MATERIALIZED VIEW - when each.
  15. Q65Refresh failure handling: alarms, partial-state safety.
  16. Q66Reconciliation suite: mv totals = base totals (assertion queries).
  17. Q67SLA report per MV: freshness (refresh-age) + size + index health.
  18. Q68Cost forecasting per refresh tier (5-min / hourly / daily).
  19. Q69Refresh-window throttling for resource contention (concept).
  20. Q70Build mv_exec_strip from many marts + UNIQUE INDEX(region).
  21. Q71Build mv_customer_360 + UNIQUE INDEX(customer_id) + cadence comment.
  22. Q72Build mv_anomaly_alerts (Topic 22+23) + UNIQUE INDEX(d, region).
  23. Q73Build mv_funnel_stage_hourly + UNIQUE INDEX(stage, hour).
  24. Q74Build mv_pricing_corridor + UNIQUE INDEX(prod_id).
  25. Q75Wire the full refresh DAG: one script, dependency-ordered, idempotent.

PRODUCTION DELIVERY SYSTEMS

  1. Q76Exec dashboard delivery: mv_exec_region_strip + JSON-export view (Topic 24).
  2. Q77SLA monitoring delivery: mv_delivery_sla + mv_ticket_sla + dashboards.
  3. Q78Pricing governance delivery: mv_pricing_corridor + drift alerts.
  4. Q79Inventory operations delivery: mv_inventory_value + cover trend + stockout streaks.
  5. Q80Funnel analytics delivery: mv_funnel_stage_hourly + per-channel breakdown.
  6. Q81Cohort retention delivery: mv_cohort_triangle_with_ltv (Topic 23).
  7. Q82RFM delivery: mv_customer_rfm + segment counts (Topic 16, Topic 27 preview).
  8. Q83Anomaly delivery: mv_anomaly_daily + alert payload JSON (Topic 24).
  9. Q84Audit-compliance delivery: mv_audit_change_jumps + actor + table summary (Topic 26 preview).
  10. Q85Heatmap product delivery: mv_heatmap_weekday_hour normalized (Topic 23).
  11. Q86Region growth delivery: mv_growth_metrics MoM/YoY + rank + alerts.
  12. Q87Customer-360 delivery: mv_customer_360_summary + JSON export (Topic 24).
  13. Q88Pay-slip equity delivery: mv_pay_slip_dept_summary (Topic 22).
  14. Q89Returns root-cause delivery: mv_returns_root_cause per category/region.
  15. Q90Dynamic-SLA delivery: mv_dynamic_sla_attainment (target = prior-Q P90, Topic 22).
  16. Q91Real-time-ish ops board: hybrid MV + live tail (concept).
  17. Q92Multi-tenant region delivery: filtered views per region.
  18. Q93Versioned semantic layer v2: rollout + deprecation script.
  19. Q94Validation suite: mv <-> base reconciliation alerts.
  20. Q95Refresh SLA report: per MV freshness + size + dependents.
  21. Q96Catalog API delivery: pricing + promo MV (Topic 24 JSON envelope).
  22. Q97Region exec JSON: mv_region_dashboard -> JSON nested doc (Topic 24).
  23. Q98Lifecycle delivery: mv_lifecycle_stage_monthly per customer.
  24. Q99End-to-end semantic-layer script: raw -> core -> marts -> exec -> JSON exports.
  25. Q100Capstone: the production semantic-layer + MV refresh DAG (raw/core/marts/exec, unique indexes, CONCURRENTLY where eligible, reconciliation, JSON exports), with cadence and dependents documented in MV comments.