Build a Semantic View

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?

select c.first_name,
       c.last_name,
       sum(oi.quantity * oi.unit_price) as revenue
from customers c
join orders o on o.customer_id = c.customer_id
join order_items 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 thestatus 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.

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 c
join orders o on o.customer_id = c.customer_id
join order_items oi on oi.order_id = o.order_id
group by c.customer_id, c.state

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 no report can forget it.

Where the risk moves to

A shared definition is a shared blast radius. CREATE OR REPLACE VIEW changes every report at once, 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.

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 theSQL views reference.