INSTALL semantic_layer FROM community;
LOAD semantic_layer;

INSTALL semantic_layer FROM community;
LOAD semantic_layer;

-- The example uses the TPC-H tables generated by DuckDB's tpch extension.
INSTALL tpch;
LOAD tpch;
CALL dbgen(sf = 0.01);

CREATE SEMANTIC VIEW sales
  TABLES (
    orders    AS orders    PRIMARY KEY (o_orderkey) TIME (o_orderdate),
    customers AS customer  PRIMARY KEY (c_custkey),
    lines     AS lineitem  PRIMARY KEY (l_orderkey, l_linenumber)
  )
  RELATIONSHIPS (
    orders_to_customers AS orders (o_custkey) REFERENCES customers,
    lines_to_orders     AS lines (l_orderkey) REFERENCES orders
  )
  FACTS (
    lines.net_price   AS l_extendedprice * (1 - l_discount),
    orders.line_count AS COUNT(lines.l_linenumber)
  )
  DIMENSIONS (
    customers.segment AS c_mktsegment,
    orders.order_year AS YEAR(o_orderdate)
  )
  METRICS (
    lines.revenue        AS SUM(lines.net_price),
    lines.revenue_ly     OFFSET (metric_time.day BY INTERVAL 1 YEAR) AS lines.revenue,
    lines.revenue_ytd    CUMULATIVE (metric_time.day TO YEAR) AS SUM(lines.net_price),
    orders.avg_order     AS AVG(o_totalprice),
    orders.avg_lines     AS AVG(orders.line_count),
    customers.customers  AS COUNT(*),
    revenue_per_customer AS lines.revenue / customers.customers,
    revenue_growth       AS lines.revenue / lines.revenue_ly - 1
  )
  COMMENT = 'TPC-H sales';

-- Joins lineitem → orders → customer, aggregates each metric at its own grain.
SELECT * FROM SEMANTIC_VIEW(
  sales
  DIMENSIONS customers.segment, orders.order_year
  METRICS lines.revenue, orders.avg_order, orders.avg_lines
  WHERE orders.order_year >= 1998
)
ORDER BY segment, order_year;

-- The same view, queried by name. Metrics are wrapped in AGG(...).
SELECT segment, AGG(revenue) AS revenue
FROM sales
WHERE order_year = 1998
GROUP BY segment
ORDER BY revenue DESC;

-- TIME (o_orderdate) adds a calendar every metric reaching orders can group by.
SELECT * FROM SEMANTIC_VIEW(
  sales
  DIMENSIONS metric_time.month
  METRICS lines.revenue, lines.revenue_ytd, revenue_growth
  WHERE metric_time.year = DATE '1997-01-01'
)
ORDER BY month;