Generate Efficient SQL Views

This skill designs efficient, maintainable, and secure SQL database views for business intelligence, reporting, and data analysis. Core principles cover logical data organization (views presenting business entities from the end-user's perspective, abstracting complex table relationships), performance (avoiding nested views more than 2-3 levels deep, minimizing complex joins in frequently accessed views, considering indexed views for heavy analytical workloads), security (using views as an access-control layer to restrict column and row visibility, with filtered views for row-level security), and maintainability (self-documenting views with clear column aliases and comments explaining business logic).

It provides three view categories with working SQL: base views (simple single-table abstractions with column renaming and basic filtering), aggregation views (pre-computed monthly sales summaries with order counts, unique customers, revenue, and cancellation counts), and dimensional views (joining orders, customers, products, and regions into a single analytical fact view). An advanced pattern uses a parameterized view built on common table expressions to compute customer lifecycle metrics - total orders, lifetime value, days since last order - then classifies each customer into a status (active, at-risk, dormant, lost) and value segment.

Security guidance covers row-level security implemented as a filtered view joined against a user-access table and scoped to the current session user, plus a standard comment-header format documenting each view's purpose, dependencies, refresh behavior, and owning team. Database-specific optimizations cover PostgreSQL materialized views with a unique index for fast refresh, and SQL Server schema-bound indexed views with a clustered index. Testing guidance covers validating performance at realistic data volumes, testing NULL and empty-result edge cases, and verifying security restrictions actually hold. Naming conventions use a vw_ prefix for views and mv_ for materialized views, with a maintained data dictionary documenting purpose, dependencies, and usage.

CREATE VIEW vw_user_accessible_orders AS
SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    c.company_name
FROM orders o
    INNER JOIN customers c ON o.customer_id = c.customer_id
    INNER JOIN user_customer_access uca ON c.customer_id = uca.customer_id
WHERE uca.user_id = SESSION_USER()
    AND uca.access_level IN ('READ', 'WRITE');

Use this skill when designing SQL views for reporting or analytics - building base, aggregation, or dimensional views, implementing row-level security, choosing between a materialized and standard view, or documenting view dependencies for a data dictionary.

It is not a fit for OLTP schema design or transactional table structure - it's scoped to the reporting/analytical view layer built on top of existing tables, not the underlying schema itself.

Inputs are the underlying tables and the business entity or metric the view needs to expose, plus any row-level access requirements. Outputs are working CREATE VIEW SQL for base, aggregation, or dimensional patterns, row-level-secured views, database-specific materialized or indexed view definitions, and a documented naming and dependency-tracking convention.

Data analysts and BI engineers building SQL views who need concrete, working patterns for base/aggregation/dimensional views, row-level security, and materialized/indexed view optimization - along with naming conventions and dependency documentation that keep a growing view layer maintainable.