What Is Malloy
Cohort Analysis
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:
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:
run: order_items -> { group_by: fiscal_year is created_at.year aggregate: total_sales order_by: fiscal_year desc }
[ { "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:
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 } }
[ { "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
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 } } }
[ { "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.