Ecommerce Revenue Lineage

A typical ecommerce revenue model: orders join customers and order_items, then roll up into a daily revenue view. See how each output column traces back to its source tables.

The lineage

The SQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email TEXT,
  country TEXT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  order_ts TIMESTAMP,
  status TEXT
);

CREATE TABLE order_items (
  item_id INT PRIMARY KEY,
  order_id INT REFERENCES orders(order_id),
  sku TEXT,
  quantity INT,
  unit_price NUMERIC(10,2)
);

CREATE VIEW v_order_revenue AS
SELECT
  o.order_id,
  o.customer_id,
  o.order_ts,
  SUM(i.quantity * i.unit_price) AS order_total
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.order_id, o.customer_id, o.order_ts;

CREATE VIEW v_daily_revenue AS
SELECT
  DATE_TRUNC('day', r.order_ts) AS revenue_day,
  c.country,
  COUNT(DISTINCT r.order_id) AS orders,
  SUM(r.order_total) AS revenue
FROM v_order_revenue r
JOIN customers c ON c.customer_id = r.customer_id
GROUP BY 1, 2;

What the lineage shows

• v_daily_revenue.revenue traces back to order_items.quantity × order_items.unit_price.
• v_daily_revenue.country comes from customers.country, not orders.
• If you ever see inflated revenue, the lineage tells you the join path to inspect first.