Views on views in SQL

A view is a query with a name, and naming the middle step is half of readable SQL.

DROP VIEW IF EXISTS paid_orders;

CREATE VIEW paid_orders AS
SELECT
    id,
    customer_id,
    total,
    placed_at
FROM orders
WHERE status = 'paid';

CREATE VIEW customer_revenue AS
SELECT
    customer_id,
    COUNT(*) AS orders_paid,
    SUM(total) AS revenue
FROM paid_orders
GROUP BY customer_id;

How it works

A view stores no rows; it runs its query every time you select from it.
Views compose, so each layer can stay short enough to read.
DROP VIEW IF EXISTS first makes the script safe to re-run.

Keywords and builtins used here

AS
BY
COUNT
CREATE
DROP
EXISTS
FROM
GROUP
IF
SELECT
SUM
VIEW
WHERE

The run, in numbers

Lines: 18
Characters to type: 275
Tokens: 51
Three-star pace: 90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 34 seconds.

Type this snippet

Step 2 of 3 in Views & scratch tables, step 11 of 16 in Schema & constraints.

← Previous Next →