SaaS metrics

/* SQL */
CREATE SCHEMA IF NOT EXISTS tutorial_saas;
--
DROP TABLE IF EXISTS tutorial_saas.support_tickets;
--
DROP TABLE IF EXISTS tutorial_saas.invoices;
--
DROP TABLE IF EXISTS tutorial_saas.subscriptions;
--
DROP TABLE IF EXISTS tutorial_saas.customers;
--
CREATE TABLE tutorial_saas.customers (
  id BIGINT,
  name VARCHAR,
  segment VARCHAR,
  created_at DATE
);
--
INSERT INTO tutorial_saas.customers VALUES
  (1, 'Acme', 'enterprise', DATE '2024-01-15'),
  (2, 'Beta', 'smb', DATE '2024-01-20'),
  (3, 'Gamma', 'enterprise', DATE '2024-02-10'),
  (4, 'Delta', 'smb', DATE '2024-03-01'),
  (5, 'Epsilon', 'smb', DATE '2024-03-15');
--
CREATE TABLE tutorial_saas.subscriptions (
  id BIGINT,
  customer_id BIGINT,
  plan VARCHAR,
  mrr BIGINT,
  started_at DATE,
  ended_at DATE,
  inactive_on DATE
);
--
INSERT INTO tutorial_saas.subscriptions VALUES
  (1, 1, 'pro', 500, DATE '2024-01-15', NULL, DATE '9999-12-31'),
  (2, 2, 'starter', 100, DATE '2024-01-20', DATE '2024-04-15', DATE '2024-04-15'),
  (3, 3, 'pro', 500, DATE '2024-02-10', NULL, DATE '9999-12-31'),
  (4, 4, 'starter', 100, DATE '2024-03-01', DATE '2024-05-20', DATE '2024-05-20'),
  (5, 5, 'starter', 100, DATE '2024-03-15', NULL, DATE '2024-06-01'),
  (6, 2, 'pro', 500, DATE '2024-06-01', NULL, DATE '9999-12-31'),
  (7, 5, 'pro', 300, DATE '2024-06-01', NULL, DATE '9999-12-31');
--
CREATE TABLE tutorial_saas.invoices (
  id BIGINT,
  subscription_id BIGINT,
  customer_id BIGINT,
  amount BIGINT,
  issued_at DATE,
  paid_at DATE
);
--
INSERT INTO tutorial_saas.invoices VALUES
  (1, 1, 1, 500, DATE '2024-02-01', DATE '2024-02-05'),
  (2, 2, 2, 100, DATE '2024-02-01', DATE '2024-02-10'),
  (3, 1, 1, 500, DATE '2024-03-01', DATE '2024-03-03'),
  (4, 2, 2, 100, DATE '2024-03-01', DATE '2024-03-15'),
  (5, 3, 3, 500, DATE '2024-03-01', DATE '2024-03-05'),
  (6, 1, 1, 500, DATE '2024-04-01', DATE '2024-04-02'),
  (7, 4, 4, 100, DATE '2024-04-01', DATE '2024-04-20'),
  (8, 3, 3, 500, DATE '2024-04-01', DATE '2024-04-03'),
  (9, 5, 5, 100, DATE '2024-04-01', DATE '2024-04-10'),
  (10, 1, 1, 500, DATE '2024-05-01', DATE '2024-05-04'),
  (11, 3, 3, 500, DATE '2024-05-01', DATE '2024-05-02'),
  (12, 4, 4, 100, DATE '2024-05-01', NULL);
--
CREATE TABLE tutorial_saas.support_tickets (
  id BIGINT,
  customer_id BIGINT,
  priority VARCHAR,
  opened_at DATE,
  resolved_at DATE
);
--
INSERT INTO tutorial_saas.support_tickets VALUES
  (1, 1, 'low', DATE '2024-02-10', DATE '2024-02-11'),
  (2, 2, 'high', DATE '2024-03-20', DATE '2024-03-25'),
  (3, 2, 'high', DATE '2024-04-01', NULL),
  (4, 4, 'critical', DATE '2024-04-15', DATE '2024-04-18'),
  (5, 5, 'low', DATE '2024-05-01', DATE '2024-05-02'),
  (6, 3, 'medium', DATE '2024-05-10', DATE '2024-05-12');
--
SELECT CAST(COUNT(*) AS INTEGER) AS cnt FROM tutorial_saas.customers;

CREATE OR REPLACE FEATURES IN FM.SAAS AS
SELECT
    customers := ENTITY(),
    subscriptions := ENTITY(),
    invoices := ENTITY(),
    tickets := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    subscription_id := INPUT(BIGINT#subscriptions),
    invoice_id := INPUT(BIGINT#invoices),
    ticket_id := INPUT(BIGINT#tickets)
;

CREATE OR REPLACE FEATURES IN FM.SAAS AS
SELECT
    tables.customers := EXTERNAL_COLUMNS(
        id BIGINT#customers BIND TO customer_id,
        name VARCHAR,
        segment VARCHAR,
        created_at DATE
        FROM TABLE(tutorial_saas.customers)
    ),
    tables.subs := EXTERNAL_COLUMNS(
        id BIGINT#subscriptions BIND TO subscription_id,
        customer_id BIGINT#customers,
        plan VARCHAR,
        mrr BIGINT,
        started_at DATE,
        ended_at DATE,
        inactive_on DATE
        FROM TABLE(tutorial_saas.subscriptions)
    ),
    tables.invoices := EXTERNAL_COLUMNS(
        id BIGINT#invoices BIND TO invoice_id,
        subscription_id BIGINT#subscriptions,
        customer_id BIGINT#customers,
        amount BIGINT,
        issued_at DATE,
        paid_at DATE
        FROM TABLE(tutorial_saas.invoices)
    ),
    tables.tickets := EXTERNAL_COLUMNS(
        id BIGINT#tickets BIND TO ticket_id,
        customer_id BIGINT#customers,
        priority VARCHAR,
        opened_at DATE,
        resolved_at DATE
        FROM TABLE(tutorial_saas.support_tickets)
    )
;

WITH
    as_of := DATE '2024-03-31',
    started := tables.subs[started_at],
    inactive_on := tables.subs[inactive_on],
    mrr := tables.subs[mrr],
    contrib := IF(started <= as_of AND inactive_on > as_of, mrr, 0)
SELECT
    total_mrr := SUM(contrib)
FROM FM.SAAS
FOR
    subscription_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.subscriptions ORDER BY id)
    )
;

WITH
    as_of := DATE '2024-04-30',
    started := tables.subs[started_at],
    inactive_on := tables.subs[inactive_on],
    mrr := tables.subs[mrr],
    segment := RELATED(tables.customers[segment] VIA tables.subs[customer_id]),
    contrib := IF(started <= as_of AND inactive_on > as_of, mrr, 0)
SELECT
    segment,
    segment_mrr := SUM(contrib) GROUP BY segment
FROM FM.SAAS
FOR
    subscription_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.subscriptions ORDER BY id)
    )
ORDER BY segment
;

CREATE OR REPLACE FEATURES IN FM.SAAS AS
SELECT
    payment_sla_days := 10.00,
    customer_avg_days_paid_invoices := customer_id.RELATED(
        AVG(
            IF(
                tables.invoices[paid_at] IS NOT NULL,
                DATE_SUBTRACT(
                    tables.invoices[paid_at]::TIMESTAMP,
                    tables.invoices[issued_at]::TIMESTAMP,
                    'day'
                ),
                NULL(BIGINT)
            )
        )
        GROUP BY tables.invoices[customer_id]
    ),
    customer_open_ticket_count := customer_id.RELATED(
        SUM(IF(tables.tickets[resolved_at] IS NULL, 1, 0))
        GROUP BY tables.tickets[customer_id]
    ),
    customer_avg_exceeds_payment_sla := CAST(
        customer_avg_days_paid_invoices AS DECIMAL(10,2)
    )
    > CAST(payment_sla_days AS DECIMAL(10,2)),
    customer_has_open_ticket := customer_open_ticket_count > 0,
    customer_at_risk := customer_has_open_ticket
    OR customer_avg_exceeds_payment_sla,
    customer_health_status := CASE
        WHEN customer_at_risk THEN 'at risk'
        ELSE 'healthy'
    END,
    customer_open_unresolved_ticket_id := customer_id.RELATED(
        MAX(
            IF(
                tables.tickets[resolved_at] IS NULL,
                UNSAFE_CAST(tables.tickets[id] AS BIGINT),
                NULL(BIGINT)
            )
        )
        GROUP BY tables.tickets[customer_id]
    ),
    customer_open_unresolved_ticket_opened_at := customer_id.RELATED(
        MAX(
            IF(
                tables.tickets[resolved_at] IS NULL,
                tables.tickets[opened_at],
                NULL(DATE)
            )
        )
        GROUP BY tables.tickets[customer_id]
    )
;

WITH
    STATUS := CUSTOMER_HEALTH_STATUS,
SELECT
    CUSTOMER_ID,
    STATUS
FROM FM.SAAS
FOR
    CUSTOMER_ID := BIND_COLUMNS(id FROM SQL(SELECT id FROM tutorial_saas.customers ORDER BY id)),
    NESTED INVOICE_ID := BIND_COLUMNS(id FROM SQL(SELECT id FROM tutorial_saas.invoices ORDER BY id)),
    NESTED TICKET_ID := BIND_COLUMNS(id FROM SQL(SELECT id FROM tutorial_saas.support_tickets ORDER BY id))
ORDER BY CUSTOMER_ID;

WITH
    FACTOR := CASE
        WHEN CUSTOMER_OPEN_TICKET_COUNT > 0 THEN 'open_ticket'
        WHEN CUSTOMER_AVG_EXCEEDS_PAYMENT_SLA THEN 'slow_payment'
        ELSE NULL(VARCHAR)
    END,
    DETAIL_VALUE := CASE
        WHEN CUSTOMER_OPEN_TICKET_COUNT > 0 THEN CAST(CUSTOMER_OPEN_UNRESOLVED_TICKET_ID AS VARCHAR)
        WHEN CUSTOMER_AVG_EXCEEDS_PAYMENT_SLA THEN CAST(CAST(CUSTOMER_AVG_DAYS_PAID_INVOICES AS BIGINT) AS VARCHAR)
        ELSE NULL(VARCHAR)
    END,
    EXPLANATION := CASE
        WHEN CUSTOMER_OPEN_TICKET_COUNT > 0 THEN 'unresolved support ticket (id ' || CAST(CUSTOMER_OPEN_UNRESOLVED_TICKET_ID AS VARCHAR) || ', opened ' || CAST(CUSTOMER_OPEN_UNRESOLVED_TICKET_OPENED_AT AS VARCHAR) || ')'
        WHEN CUSTOMER_AVG_EXCEEDS_PAYMENT_SLA THEN 'average days to pay (' || CAST(CAST(CUSTOMER_AVG_DAYS_PAID_INVOICES AS BIGINT) AS VARCHAR) || ') exceeds payment SLA (' || CAST(CAST(PAYMENT_SLA_DAYS AS BIGINT) AS VARCHAR) || ')'
        ELSE NULL(VARCHAR)
    END,
SELECT
    CUSTOMER_ID,
    FACTOR,
    DETAIL_VALUE,
    EXPLANATION
FROM FM.SAAS
FOR
    CUSTOMER_ID := BIND_COLUMNS(id FROM SQL(SELECT id FROM tutorial_saas.customers WHERE id IN (2, 4) ORDER BY id)),
    NESTED INVOICE_ID := BIND_COLUMNS(id FROM SQL(SELECT id FROM tutorial_saas.invoices ORDER BY id)),
    NESTED TICKET_ID := BIND_COLUMNS(id FROM SQL(SELECT id FROM tutorial_saas.support_tickets ORDER BY id))
ORDER BY CUSTOMER_ID;