Lesson 18.3

Build a Semantic View

A semantic view hides the joins and fixes the metric definition, so two reports cannot quietly disagree about revenue.

Customer revenue in Garden Shop takes three tables and a decision. The tables are always the same. The decision is the one that causes arguments: do cancelled orders count?

The query everyone keeps pasting

select c.first_name,
       c.last_name,
       sum(oi.quantity * oi.unit_price) as revenue
from customers as c
join orders as o on o.customer_id = c.customer_id
join order_items as oi on oi.order_id = o.order_id
where o.status <> 'cancelled'
group by c.first_name, c.last_name
order by revenue desc, c.last_name

Paste that into five reports and you have five copies of a rule that only one person remembers. When someone writes the sixth report and forgets the status filter, the numbers disagree and nobody can say which is right. That is metric drift, and it is a definition problem rather than a SQL problem.

Saving the query is the easy half

Putting the join behind a name takes one statement, and it already earns its keep: a report becomes a short query against something that looks like a table. The joins still run underneath, because a view stores the query rather than the rows, but nobody has to write them again.

The join, saved under a name

create or replace view customer_revenue as
select c.customer_id,
       c.state,
       round(sum(oi.quantity * oi.unit_price), 2) as revenue
from customers as c
join orders as o on o.customer_id = c.customer_id
join order_items as oi on oi.order_id = o.order_id
group by c.customer_id, c.state

A report on top of the view

select state,
       round(sum(revenue), 2) as revenue
from customer_revenue
group by state
order by revenue desc, state

What makes a view "semantic"

That first version is a shortcut, not yet a definition. A semantic view is one written for the people reading it rather than for the database, and three habits do most of the work: give every column the name a colleague would use out loud, put the business rules inside the view where they cannot be forgotten, and expose only what a report needs.

Applied here, that means customer_name instead of two name columns to concatenate, an order count next to the revenue, and the cancelled-order rule moved inside the view so reports that use this view inherit the same rule.

Where the risk moves to

A shared definition is a shared blast radius. CREATE OR REPLACE VIEW changes the shared definition for every downstream query that depends on it, which is the point and also the danger: rename or drop a column and the reports built on it break the next time they run. Add columns freely, and treat removing one as a change worth announcing.

Syntax reference

Need syntax details? See the SQL views reference

Schema · Garden Shop Tables · order_items, customers, orders 3 tables

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Replace customer_revenue with the semantic version: customer_id, customer_name (first and last name joined by a space), state, orders (distinct order count), and revenue (rounded to 2 decimals), excluding cancelled orders. Then select customer_name, state, orders, and revenue from the view for the top 5 by revenue.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: customer_name, state, orders, revenue.
Rows: 5, the top spenders by revenue.
Sofia Greer leads on 132 across 2 orders; cancelled orders are excluded by the view.

The view is now the answer to "what do we mean by revenue". For the full syntax, including how to inspect and drop a view, see the SQL views reference.

Previous · 18.2 Check data quality with SQL

Next · 18.4 Stage, clean, and model →

Reference

SQL Views

Save a query under a name and reuse it like a table.

Reference

SQL CREATE TABLE AS SELECT

Store a query result as a table you can query again.

Reference

SQL Common Table Expressions

The WITH clause, chained steps, and CTE vs subquery.

Lesson