Malloy Documentation
search

Nested subtotals are quite painful to do in SQL, requiring either self-joins, window functions, or GROUP BY ROLLUP queries. Unfortunately for analysts, it's also a very common type of analysis requested by business owners; for example, determing which segments of a population drove revenue growth, or drilling down from annual numbers into more granular time periods, like quarters or months.

To see how we do this in Malloy, let's look at the following simple model:

document
source: order_items is duckdb.table('../data/order_items.parquet') extend {
  primary_key: id

  measure:
    total_sales is sale_price.sum()
}

This contains a single table of order items, and a measure to calculate sales. We can use this to calculate sales by year:

document
run: order_items -> {
  group_by: fiscal_year is created_at.year
  aggregate: total_sales
  order_by: fiscal_year desc
}
QUERY RESULTS
[
  {
    "fiscal_year": "2022-01-01T00:00:00.000Z",
    "total_sales": 4744743.446900487
  },
  {
    "fiscal_year": "2021-01-01T00:00:00.000Z",
    "total_sales": 3900910.8143820763
  },
  {
    "fiscal_year": "2020-01-01T00:00:00.000Z",
    "total_sales": 2602043.069360018
  },
  {
    "fiscal_year": "2019-01-01T00:00:00.000Z",
    "total_sales": 1318595.5448319912
  }
]
SELECT 
   DATE_TRUNC('year', base."created_at") as "fiscal_year",
   COALESCE(SUM(base."sale_price"),0) as "total_sales"
FROM '../data/order_items.parquet' as base
GROUP BY 1
ORDER BY 1 desc NULLS LAST

Now suppose we want to drill into the sales numbers by quarter. To do this in SQL would require either a window function, a self-join, or a GROUP BY ROLLUP, all of which are complicated to implement, and produce results that are difficult to interpret. In Malloy, none of this is necessary, we simply use a nest clause:

document
run: order_items -> {
  group_by: fiscal_year is created_at.year
  aggregate: total_sales
  order_by: fiscal_year desc

  nest: by_quarter is {
    group_by: fiscal_quarter is created_at.quarter
    aggregate: total_sales
    order_by: fiscal_quarter 
  }
}
QUERY RESULTS
[
  {
    "fiscal_year": "2022-01-01T00:00:00.000Z",
    "total_sales": 4744743.446900487,
    "by_quarter": [
      {
        "fiscal_quarter": "2022-01-01T00:00:00.000Z",
        "total_sales": 1222323.153985262
      },
      {
        "fiscal_quarter": "2022-04-01T00:00:00.000Z",
        "total_sales": 1302098.9644565582
      },
      {
        "fiscal_quarter": "2022-07-01T00:00:00.000Z",
        "total_sales": 1454325.4955222607
      },
      {
        "fiscal_quarter": "2022-10-01T00:00:00.000Z",
        "total_sales": 765995.8329364061
      }
    ]
  },
  {
    "fiscal_year": "2021-01-01T00:00:00.000Z",
    "total_sales": 3900910.8143820763,
    "by_quarter": [
      {
        "fiscal_quarter": "2021-01-01T00:00:00.000Z",
        "total_sales": 848129.9632571936
      },
      {
        "fiscal_quarter": "2021-04-01T00:00:00.000Z",
        "total_sales": 877377.0331277847
      },
      {
        "fiscal_quarter": "2021-07-01T00:00:00.000Z",
        "total_sales": 1010409.5437389612
      },
      {
        "fiscal_quarter": "2021-10-01T00:00:00.000Z",
        "total_sales": 1164994.2742581367
      }
    ]
  },
  {
    "fiscal_year": "2020-01-01T00:00:00.000Z",
    "total_sales": 2602043.069360018,
    "by_quarter": [
      {
        "fiscal_quarter": "2020-01-01T00:00:00.000Z",
        "total_sales": 531607.7420347929
      },
      {
        "fiscal_quarter": "2020-04-01T00:00:00.000Z",
        "total_sales": 578336.4721107483
      },
      {
        "fiscal_quarter": "2020-07-01T00:00:00.000Z",
        "total_sales": 671472.1522718668
      },
      {
        "fiscal_quarter": "2020-10-01T00:00:00.000Z",
        "total_sales": 820626.7029426098
      }
    ]
  },
  {
    "fiscal_year": "2019-01-01T00:00:00.000Z",
    "total_sales": 1318595.5448319912,
    "by_quarter": [
      {
        "fiscal_quarter": "2019-01-01T00:00:00.000Z",
        "total_sales": 187058.77078318596
      },
      {
        "fiscal_quarter": "2019-04-01T00:00:00.000Z",
        "total_sales": 277457.2608318329
      },
      {
        "fiscal_quarter": "2019-07-01T00:00:00.000Z",
        "total_sales": 364416.3513685465
      },
      {
        "fiscal_quarter": "2019-10-01T00:00:00.000Z",
        "total_sales": 489663.16184842587
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    DATE_TRUNC('year', base."created_at") as "fiscal_year__0",
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__0",
    CASE WHEN group_set=1 THEN
      DATE_TRUNC('quarter', base."created_at")
      END as "fiscal_quarter__1",
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__1"
  FROM '../data/order_items.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,4
)
SELECT
  "fiscal_year__0" as "fiscal_year",
  MAX(CASE WHEN group_set=0 THEN "total_sales__0" END) as "total_sales",
  COALESCE(LIST({
    "fiscal_quarter": "fiscal_quarter__1", 
    "total_sales": "total_sales__1"}  ORDER BY  "fiscal_quarter__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_quarter"
FROM __stage0
GROUP BY 1
ORDER BY 1 desc NULLS LAST

To drill down even further, it's trivial to repeat this pattern once again. The following query looks at the top 5 sales days for each fiscal quarter

document
run: order_items -> {
  group_by: fiscal_year is created_at.year
  aggregate: total_sales
  order_by: fiscal_year desc

  nest: by_quarter is  {
    group_by: fiscal_quarter is created_at.quarter
    aggregate: total_sales
    order_by: fiscal_quarter 

    nest: top_days is  {
      group_by: sale_date is created_at.day
      aggregate: total_sales
      limit: 5
    }
  }
}
QUERY RESULTS
[
  {
    "fiscal_year": "2022-01-01T00:00:00.000Z",
    "total_sales": 4744743.446900487,
    "by_quarter": [
      {
        "fiscal_quarter": "2022-01-01T00:00:00.000Z",
        "total_sales": 1222323.153985262,
        "top_days": [
          {
            "sale_date": "2022-03-31T00:00:00.000Z",
            "total_sales": 15838.000048160553
          },
          {
            "sale_date": "2022-03-30T00:00:00.000Z",
            "total_sales": 15060.81005692482
          },
          {
            "sale_date": "2022-03-29T00:00:00.000Z",
            "total_sales": 14276.180056810379
          },
          {
            "sale_date": "2022-03-28T00:00:00.000Z",
            "total_sales": 16135.75002336502
          },
          {
            "sale_date": "2022-03-27T00:00:00.000Z",
            "total_sales": 9974.510008335114
          }
        ]
      },
      {
        "fiscal_quarter": "2022-04-01T00:00:00.000Z",
        "total_sales": 1302098.9644565582,
        "top_days": [
          {
            "sale_date": "2022-06-30T00:00:00.000Z",
            "total_sales": 14788.70004415512
          },
          {
            "sale_date": "2022-06-29T00:00:00.000Z",
            "total_sales": 15081.440027952194
          },
          {
            "sale_date": "2022-06-28T00:00:00.000Z",
            "total_sales": 15985.900051116943
          },
          {
            "sale_date": "2022-06-27T00:00:00.000Z",
            "total_sales": 17816.170053720474
          },
          {
            "sale_date": "2022-06-26T00:00:00.000Z",
            "total_sales": 10816.15003323555
          }
        ]
      },
      {
        "fiscal_quarter": "2022-07-01T00:00:00.000Z",
        "total_sales": 1454325.4955222607,
        "top_days": [
          {
            "sale_date": "2022-09-30T00:00:00.000Z",
            "total_sales": 18377.750062704086
          },
          {
            "sale_date": "2022-09-29T00:00:00.000Z",
            "total_sales": 18631.130081653595
          },
          {
            "sale_date": "2022-09-28T00:00:00.000Z",
            "total_sales": 16604.310045957565
          },
          {
            "sale_date": "2022-09-27T00:00:00.000Z",
            "total_sales": 21601.170073747635
          },
          {
            "sale_date": "2022-09-26T00:00:00.000Z",
            "total_sales": 21291.520059108734
          }
        ]
      },
      {
        "fiscal_quarter": "2022-10-01T00:00:00.000Z",
        "total_sales": 765995.8329364061,
        "top_days": [
          {
            "sale_date": "2022-11-14T00:00:00.000Z",
            "total_sales": 11470.220048904419
          },
          {
            "sale_date": "2022-11-13T00:00:00.000Z",
            "total_sales": 12687.750051021576
          },
          {
            "sale_date": "2022-11-12T00:00:00.000Z",
            "total_sales": 14292.660077095032
          },
          {
            "sale_date": "2022-11-11T00:00:00.000Z",
            "total_sales": 16805.99007320404
          },
          {
            "sale_date": "2022-11-10T00:00:00.000Z",
            "total_sales": 19224.200048446655
          }
        ]
      }
    ]
  },
  {
    "fiscal_year": "2021-01-01T00:00:00.000Z",
    "total_sales": 3900910.8143820763,
    "by_quarter": [
      {
        "fiscal_quarter": "2021-01-01T00:00:00.000Z",
        "total_sales": 848129.9632571936,
        "top_days": [
          {
            "sale_date": "2021-03-31T00:00:00.000Z",
            "total_sales": 8207.750026464462
          },
          {
            "sale_date": "2021-03-30T00:00:00.000Z",
            "total_sales": 10024.950029373169
          },
          {
            "sale_date": "2021-03-29T00:00:00.000Z",
            "total_sales": 11538.270048618317
          },
          {
            "sale_date": "2021-03-28T00:00:00.000Z",
            "total_sales": 7214.580015301704
          },
          {
            "sale_date": "2021-03-27T00:00:00.000Z",
            "total_sales": 6354.590037822723
          }
        ]
      },
      {
        "fiscal_quarter": "2021-04-01T00:00:00.000Z",
        "total_sales": 877377.0331277847,
        "top_days": [
          {
            "sale_date": "2021-06-30T00:00:00.000Z",
            "total_sales": 9473.950018882751
          },
          {
            "sale_date": "2021-06-29T00:00:00.000Z",
            "total_sales": 10071.450026988983
          },
          {
            "sale_date": "2021-06-28T00:00:00.000Z",
            "total_sales": 12266.550037384033
          },
          {
            "sale_date": "2021-06-27T00:00:00.000Z",
            "total_sales": 9190.42005109787
          },
          {
            "sale_date": "2021-06-26T00:00:00.000Z",
            "total_sales": 8406.03002500534
          }
        ]
      },
      {
        "fiscal_quarter": "2021-07-01T00:00:00.000Z",
        "total_sales": 1010409.5437389612,
        "top_days": [
          {
            "sale_date": "2021-09-30T00:00:00.000Z",
            "total_sales": 15300.07003736496
          },
          {
            "sale_date": "2021-09-29T00:00:00.000Z",
            "total_sales": 11604.310046195984
          },
          {
            "sale_date": "2021-09-28T00:00:00.000Z",
            "total_sales": 16057.420017004013
          },
          {
            "sale_date": "2021-09-27T00:00:00.000Z",
            "total_sales": 13798.09006011486
          },
          {
            "sale_date": "2021-09-26T00:00:00.000Z",
            "total_sales": 10735.400017261505
          }
        ]
      },
      {
        "fiscal_quarter": "2021-10-01T00:00:00.000Z",
        "total_sales": 1164994.2742581367,
        "top_days": [
          {
            "sale_date": "2021-12-31T00:00:00.000Z",
            "total_sales": 8400.810025930405
          },
          {
            "sale_date": "2021-12-30T00:00:00.000Z",
            "total_sales": 14864.990024089813
          },
          {
            "sale_date": "2021-12-29T00:00:00.000Z",
            "total_sales": 12166.21004152298
          },
          {
            "sale_date": "2021-12-28T00:00:00.000Z",
            "total_sales": 17893.940051555634
          },
          {
            "sale_date": "2021-12-27T00:00:00.000Z",
            "total_sales": 16780.93005490303
          }
        ]
      }
    ]
  },
  {
    "fiscal_year": "2020-01-01T00:00:00.000Z",
    "total_sales": 2602043.069360018,
    "by_quarter": [
      {
        "fiscal_quarter": "2020-01-01T00:00:00.000Z",
        "total_sales": 531607.7420347929,
        "top_days": [
          {
            "sale_date": "2020-03-31T00:00:00.000Z",
            "total_sales": 8762.63003373146
          },
          {
            "sale_date": "2020-03-30T00:00:00.000Z",
            "total_sales": 6353.270024061203
          },
          {
            "sale_date": "2020-03-29T00:00:00.000Z",
            "total_sales": 4788.210015296936
          },
          {
            "sale_date": "2020-03-28T00:00:00.000Z",
            "total_sales": 5371.490037918091
          },
          {
            "sale_date": "2020-03-27T00:00:00.000Z",
            "total_sales": 6531.560037612915
          }
        ]
      },
      {
        "fiscal_quarter": "2020-04-01T00:00:00.000Z",
        "total_sales": 578336.4721107483,
        "top_days": [
          {
            "sale_date": "2020-06-30T00:00:00.000Z",
            "total_sales": 7549.300024032593
          },
          {
            "sale_date": "2020-06-29T00:00:00.000Z",
            "total_sales": 7506.210007190704
          },
          {
            "sale_date": "2020-06-28T00:00:00.000Z",
            "total_sales": 5140.560017585754
          },
          {
            "sale_date": "2020-06-27T00:00:00.000Z",
            "total_sales": 4646.8400230407715
          },
          {
            "sale_date": "2020-06-26T00:00:00.000Z",
            "total_sales": 6472.60002207756
          }
        ]
      },
      {
        "fiscal_quarter": "2020-07-01T00:00:00.000Z",
        "total_sales": 671472.1522718668,
        "top_days": [
          {
            "sale_date": "2020-09-30T00:00:00.000Z",
            "total_sales": 8241.390033960342
          },
          {
            "sale_date": "2020-09-29T00:00:00.000Z",
            "total_sales": 9371.40002655983
          },
          {
            "sale_date": "2020-09-28T00:00:00.000Z",
            "total_sales": 9755.480032444
          },
          {
            "sale_date": "2020-09-27T00:00:00.000Z",
            "total_sales": 5392.870006084442
          },
          {
            "sale_date": "2020-09-26T00:00:00.000Z",
            "total_sales": 5581.3900146484375
          }
        ]
      },
      {
        "fiscal_quarter": "2020-10-01T00:00:00.000Z",
        "total_sales": 820626.7029426098,
        "top_days": [
          {
            "sale_date": "2020-12-31T00:00:00.000Z",
            "total_sales": 5959.610029220581
          },
          {
            "sale_date": "2020-12-30T00:00:00.000Z",
            "total_sales": 12423.390022277832
          },
          {
            "sale_date": "2020-12-29T00:00:00.000Z",
            "total_sales": 12249.600047111511
          },
          {
            "sale_date": "2020-12-28T00:00:00.000Z",
            "total_sales": 12106.750036001205
          },
          {
            "sale_date": "2020-12-27T00:00:00.000Z",
            "total_sales": 7321.610027313232
          }
        ]
      }
    ]
  },
  {
    "fiscal_year": "2019-01-01T00:00:00.000Z",
    "total_sales": 1318595.5448319912,
    "by_quarter": [
      {
        "fiscal_quarter": "2019-01-01T00:00:00.000Z",
        "total_sales": 187058.77078318596,
        "top_days": [
          {
            "sale_date": "2019-03-31T00:00:00.000Z",
            "total_sales": 1572.800009727478
          },
          {
            "sale_date": "2019-03-30T00:00:00.000Z",
            "total_sales": 1326.0300011634827
          },
          {
            "sale_date": "2019-03-29T00:00:00.000Z",
            "total_sales": 1853.890013217926
          },
          {
            "sale_date": "2019-03-28T00:00:00.000Z",
            "total_sales": 2834.880018234253
          },
          {
            "sale_date": "2019-03-27T00:00:00.000Z",
            "total_sales": 2447.680019378662
          }
        ]
      },
      {
        "fiscal_quarter": "2019-04-01T00:00:00.000Z",
        "total_sales": 277457.2608318329,
        "top_days": [
          {
            "sale_date": "2019-06-30T00:00:00.000Z",
            "total_sales": 1712.3700094223022
          },
          {
            "sale_date": "2019-06-29T00:00:00.000Z",
            "total_sales": 2904.3700094223022
          },
          {
            "sale_date": "2019-06-28T00:00:00.000Z",
            "total_sales": 2507.899995326996
          },
          {
            "sale_date": "2019-06-27T00:00:00.000Z",
            "total_sales": 3338.9600014686584
          },
          {
            "sale_date": "2019-06-26T00:00:00.000Z",
            "total_sales": 4174.500011444092
          }
        ]
      },
      {
        "fiscal_quarter": "2019-07-01T00:00:00.000Z",
        "total_sales": 364416.3513685465,
        "top_days": [
          {
            "sale_date": "2019-09-30T00:00:00.000Z",
            "total_sales": 5101.670015335083
          },
          {
            "sale_date": "2019-09-29T00:00:00.000Z",
            "total_sales": 4670.120021343231
          },
          {
            "sale_date": "2019-09-28T00:00:00.000Z",
            "total_sales": 3791.18000125885
          },
          {
            "sale_date": "2019-09-27T00:00:00.000Z",
            "total_sales": 4934.630023479462
          },
          {
            "sale_date": "2019-09-26T00:00:00.000Z",
            "total_sales": 6435.990019798279
          }
        ]
      },
      {
        "fiscal_quarter": "2019-10-01T00:00:00.000Z",
        "total_sales": 489663.16184842587,
        "top_days": [
          {
            "sale_date": "2019-12-31T00:00:00.000Z",
            "total_sales": 2834.770021915436
          },
          {
            "sale_date": "2019-12-30T00:00:00.000Z",
            "total_sales": 7551.170014858246
          },
          {
            "sale_date": "2019-12-29T00:00:00.000Z",
            "total_sales": 3886.420011997223
          },
          {
            "sale_date": "2019-12-28T00:00:00.000Z",
            "total_sales": 4340.540011405945
          },
          {
            "sale_date": "2019-12-27T00:00:00.000Z",
            "total_sales": 6892.750037193298
          }
        ]
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    DATE_TRUNC('year', base."created_at") as "fiscal_year__0",
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__0",
    CASE WHEN group_set IN (1,2) THEN
      DATE_TRUNC('quarter', base."created_at")
      END as "fiscal_quarter__1",
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__1",
    CASE WHEN group_set=2 THEN
      DATE_TRUNC('day', base."created_at")
      END as "sale_date__2",
    CASE WHEN group_set=2 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__2"
  FROM '../data/order_items.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  GROUP BY 1,2,4,6
)
, __stage1 AS (
  SELECT 
    CASE WHEN group_set=2 THEN 1  ELSE group_set END as group_set,
    "fiscal_year__0" as "fiscal_year__0",
    FIRST("total_sales__0") FILTER (WHERE "total_sales__0" IS NOT NULL) as "total_sales__0",
    CASE WHEN group_set IN (1,2) THEN
      "fiscal_quarter__1"
      END as "fiscal_quarter__1",
    FIRST("total_sales__1") FILTER (WHERE "total_sales__1" IS NOT NULL) as "total_sales__1",
    COALESCE(LIST({
      "sale_date": "sale_date__2", 
      "total_sales": "total_sales__2"}  ORDER BY  "sale_date__2" desc NULLS LAST) FILTER (WHERE group_set=2)[1:5],[]) as "top_days__1"
  FROM __stage0
  GROUP BY 1,2,4
)
SELECT
  "fiscal_year__0" as "fiscal_year",
  MAX(CASE WHEN group_set=0 THEN "total_sales__0" END) as "total_sales",
  COALESCE(LIST({
    "fiscal_quarter": "fiscal_quarter__1", 
    "total_sales": "total_sales__1", 
    "top_days": "top_days__1"}  ORDER BY  "fiscal_quarter__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_quarter"
FROM __stage1
GROUP BY 1
ORDER BY 1 desc NULLS LAST

These queries are trivial to implement, and easy to understand. If you'd like a challenge, try implementing the same thing in SQL and see what it looks like.