Nested Query Example

queries.sql

-- Option 1:
-- Multiple sub-queries joined together:
WITH yearly_sales AS (
  SELECT
    YEAR(d_date) AS d_year
    , ROUND(SUM(total_sales), 2) AS yearly_sales
  FROM sales_by_date
  GROUP BY 1
),

monthly_sales AS (
  SELECT
    YEAR(d_date) AS d_year
    , MONTH(d_date) AS d_month
    , ROUND(SUM(total_sales), 2) AS monthly_sales
  FROM sales_by_date
  GROUP BY 1,2
) 

SELECT
  m.d_year
  , m.d_month
  , y.yearly_sales 
  , m.monthly_sales
FROM yearly_sales y
JOIN monthly_sales m  ON y.d_year = m.d_year
ORDER BY 1,2
;

-- Option 2:
-- Window functions
SELECT DISTINCT
  DATE_TRUNC('month', d_date) AS d_month
  , SUM(total_sales) OVER (PARTITION BY YEAR(d_date)) AS year_sales
  , SUM(total_sales) OVER (PARTITION BY YEAR(d_date), MONTH(d_date)) AS month_sales
FROM sales_by_date
ORDER BY 1
;

-- Option 3:
-- GROUP BY ROLLUP
SELECT 
  YEAR(d_date) AS d_year
  , MONTH(d_date) AS d_month
  , ROUND(SUM(total_sales), 2) AS total_sales
FROM sales_by_date 
GROUP BY ROLLUP (d_year, d_month)
ORDER BY d_year NULLS FIRST, d_month NULLS FIRST
;

query.malloy

query: sales_by_date -> {
  group_by: d_year is d_date.year
  aggregate: yearly_sales is sum(total_sales)
  order_by: d_year

  nest: by_month is {
    group_by: d_month is d_date.month
    aggregate: monthly_sales is sum(total_sales)
    order_by: d_month
  }
}

results_with_duplicates.md

d_year	d_month	yearly_sales	monthly_sales
1998	1	20366905.98	965585.91
1998	2	20366905.98	896128.67
1998	3	20366905.98	1001190.62
1998	4	20366905.98	937405.91
1998	5	20366905.98	1005053.78
1998	6	20366905.98	975344.44
1998	7	20366905.98	1024056.74
1998	8	20366905.98	2183082.06
1998	9	20366905.98	2299383.39
1998	10	20366905.98	2339050.35
1998	11	20366905.98	3269425.39
1998	12	20366905.98	3471198.72

results_with_nulls.markdown

d_year	d_month	total_sales
		20366905.98
1998		20366905.98
1998	1	965585.91
1998	2	896128.67
1998	3	1001190.62
1998	4	937405.91
1998	5	1005053.78
1998	6	975344.44
1998	7	1024056.74
1998	8	2183082.06
1998	9	2299383.39
1998	10	2339050.35
1998	11	3269425.39
1998	12	3471198.72

sales_by_date.markdown

d_date	total_sales
1998-01-02	35740.32
1998-01-03	28482.97
1998-01-04	41593.27
1998-01-05	30177.94
1998-01-06	31860.6
1998-01-07	32308.11
1998-01-08	33038.88
1998-01-09	28326.52
1998-01-10	29002.37
1998-01-11	33717.35