apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: region_ytd
  description: Revenue year to date (January to REPORT_MONTH) per region in kEUR
spec:
  dependencies: [kestrel]
  query: |
    SELECT
      region AS category,
      CASE region WHEN 'North' THEN 1 WHEN 'South' THEN 2 WHEN 'East' THEN 3
                  WHEN 'West' THEN 4 WHEN 'Export' THEN 5 END AS categoryIndex,
      SUM(revenue_ac) / 1000.0 AS ac1,
      SUM(revenue_pl) / 1000.0 AS pl1,
      SUM(revenue_py) / 1000.0 AS pp1
    FROM kestrel
    WHERE month(month) <= ${REPORT_MONTH:12}
    GROUP BY region
    ORDER BY categoryIndex