apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: monthly
  description: >-
    Revenue per month in kEUR. Months up to REPORT_MONTH are actual (ac1), later months are a
    run-rate forecast (fc1): plan times the year-to-date ratio of actual to plan.
spec:
  dependencies: [kestrel]
  query: |
    WITH m AS (
      SELECT month, SUM(revenue_ac) AS ac, SUM(revenue_pl) AS pl, SUM(revenue_py) AS py
      FROM kestrel GROUP BY month
    ),
    rate AS (
      SELECT SUM(ac) / SUM(pl) AS r FROM m WHERE month(month) <= ${REPORT_MONTH:12}
    )
    SELECT
      strftime(month, '%Y-%m-%d') AS date,
      CASE WHEN month(month) <= ${REPORT_MONTH:12} THEN ROUND(ac / 1000) END AS ac1,
      CASE WHEN month(month) > ${REPORT_MONTH:12} THEN ROUND(pl * rate.r / 1000) END AS fc1,
      ROUND(pl / 1000) AS pl1,
      ROUND(py / 1000) AS pp1
    FROM m, rate
    ORDER BY month