.. _derived_metrics:

Derived metrics
===============

**Derived metrics** are metrics that are computed from other metrics, which can be themselves derived metrics or "leaf" metrics (metrics with an aggregation expression). Like in the examples before, derived metrics are created through database views, but with one major difference: the SQL does a ``CROSS JOIN`` of the referenced metrics.

----

.. container:: db-selector

   Database selector loading...

Let's look at a simple example. Suppose we have these tables:

.. code-block:: sql
   :class: db-example db-sqlite

   PRAGMA foreign_keys = ON;

   CREATE TABLE dim_products (
     id INTEGER PRIMARY KEY,
     category TEXT
   );

   CREATE TABLE fact_orders (
     id INTEGER PRIMARY KEY,
     product_id INTEGER,
     amount INTEGER,
     FOREIGN KEY (product_id) REFERENCES dim_products(id)
   );

.. code-block:: sql
   :class: db-example db-duckdb

   CREATE TABLE dim_products (
     id INTEGER,
     category TEXT
   );

   CREATE TABLE fact_orders (
     id INTEGER,
     product_id INTEGER,
     amount INTEGER,
     FOREIGN KEY (product_id) REFERENCES dim_products(id)
   );

.. code-block:: sql
   :class: db-example db-postgres

   CREATE TABLE dim_products (
     id SERIAL PRIMARY KEY,
     category TEXT
   );

   CREATE TABLE fact_orders (
     id SERIAL PRIMARY KEY,
     product_id INTEGER REFERENCES dim_products(id),
     amount INTEGER
   );

We create a couple leaf metrics:

.. code-block:: sql
   :class: db-example db-sqlite

   CREATE VIEW total_orders AS
   SELECT SUM(amount) AS "total_orders [type=INTEGER]"
   FROM fact_orders;

   CREATE VIEW order_count AS
   SELECT COUNT(*) AS "order_count [type=INTEGER]"
   FROM fact_orders;

.. code-block:: sql
   :class: db-example db-duckdb db-postgres

   CREATE VIEW total_orders AS
   SELECT SUM(amount) AS total_orders
   FROM fact_orders;

   CREATE VIEW order_count AS
   SELECT COUNT(*) AS order_count
   FROM fact_orders;

Now, to define a derived metrics we do a ``CROSS JOIN`` between the 2 views:

.. code-block:: sql
   :class: db-example db-sqlite

   CREATE VIEW avg_order_value AS
   SELECT 1.0 * total_orders / NULLIF(order_count, 0) AS "avg_order_value [type=REAL]"
   FROM total_orders
   CROSS JOIN order_count;

.. code-block:: sql
   :class: db-example db-duckdb db-postgres

   CREATE VIEW avg_order_value AS
   SELECT 1.0 * total_orders / NULLIF(order_count, 0) AS avg_order_value
   FROM total_orders
   CROSS JOIN order_count;

Why a cross join? Since leaf metrics must be defined without a fixed grain, doing a cross join allows the overall value of the derived metric to be computed by simply querying the view. In our example:

.. code-block:: sql
   :class: db-example db-sqlite db-duckdb db-postgres

   SELECT * FROM total_orders;
   350
   SELECT * FROM order_count;
   3
   SELECT * FROM avg_order_value;  -- 350/3
   116.666666666667

It's also possible to create derived metrics from other derived metrics, or even a combination of leaf and derived metrics. Note that we shouldn't use a cross join when the derived metrics reads from a single metric, for example:

.. code-block:: sql
   :class: db-example db-sqlite

   CREATE VIEW avg_order_value_plus_one AS
   SELECT avg_order_value + 1 AS "avg_order_value_plus_one [type=REAL]"
   FROM avg_order_value;

   CREATE VIEW avg_order_value_plus_two AS
   SELECT avg_order_value_plus_one + 1 AS "avg_order_value_plus_two [type=REAL]"
   FROM avg_order_value_plus_one;

.. code-block:: sql
   :class: db-example db-duckdb db-postgres

   CREATE VIEW avg_order_value_plus_one AS
   SELECT avg_order_value + 1 AS avg_order_value_plus_one
   FROM avg_order_value;

   CREATE VIEW avg_order_value_plus_two AS
   SELECT avg_order_value_plus_one + 1 AS avg_order_value_plus_two
   FROM avg_order_value_plus_one;