Views in SQL

A view is a stored query you can select from like a table.

DROP VIEW IF EXISTS active_customers;

CREATE VIEW active_customers AS
SELECT
    c.id,
    c.name,
    MAX(o.placed_at) AS last_order
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
HAVING MAX(o.placed_at) >= DATE('now', '-90 days');

How it works

1. It stores the query, not the rows, so it is always current.
2. Views hide join and filter detail behind one name.
3. DROP VIEW IF EXISTS makes the definition re-runnable.

Keywords and builtins used here

• AS
• BY
• CREATE
• DATE
• DROP
• EXISTS
• FROM
• GROUP
• HAVING
• IF
• JOIN
• MAX
• ON
• SELECT
• VIEW
• WHERE
• c

The run, in numbers

Lines

13

Characters to type

281

Tokens

74

Three-star pace

90 tpm

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

Type this snippet

Step 1 of 3 in Views & scratch tables, step 10 of 16 in Schema & constraints.

← Previous Next →