Introducing MetricFlow, Transform’s open source metric framework.

SELECT
m.meetings_per_sales_person
, sales_28.gross_sales_last28
, COALESCE(m.ds, sales_28.ds) AS ds
FROM (
SELECT
  CAST(meetings AS DOUBLE) / NULLIF(sales_persons_with_a_meeting, 0) AS meetings_per_sales_person
  , ds
FROM (
  SELECT
    SUM(1) AS meetings
    , COUNT(DISTINCT m.sales_person_id) AS sales_persons_with_a_meeting
    , m.ds
  FROM DEMO_DB.MEETINGS AS m
  JOIN DEMO_DB.PRODUCTS AS p
    ON p.product_id = m.product_id
    AND p.ds = m.ds
  WHERE p.category = 'API'
  GROUP BY m.ds
    )
) AS m
FULL OUTER JOIN (
  SELECT
    SUM(s.gross_sales) AS gross_sales_last28
    , d.ds
    FROM (
      SELECT
        SUM(t.sales_price) AS gross_sales
        , t.ds
      FROM DEMO_DB.TRANSACTIONS AS t
      JOIN DEMO_DB.PRODUCTS AS p
        ON p.product_id = t.product_id
        AND p.ds = t.ds
      WHERE p.category = 'API'
      GROUP BY
        t.ds
    ) s
     INNER JOIN (
      SELECT DISTINCT(ds) as ds
      FROM DEMO_DB.TRANSACTIONS
    ) AS d
    ON s.ds <= d.ds
    AND s.ds > DATEADD(day, -28, d.ds)
  GROUP BY
    d.ds
) AS sales_28
ON
sales_28.ds = COALESCE(m.ds,NULL)

mql query --metrics 'meetings_per_sales_person,
gross_sales_last28' --dimensions 'ds' --where "product__category = 'API'"