Malloy Documentation
search

There are a couple of different ways to go about this in Malloy.

Method 1: Pivoting a Visualization

We can compare performance of different years using the same x and y-axes. Line charts take the x-axis, y-axis and dimensional (color) axis as parameters. In this case, the x-axis is month_of_year, the y-axis is flight_count and the dimensional (color) axis is the year.

document
source: flights is duckdb.table('../data/flights.parquet') extend {
  measure: flight_count is count()
}
document
run: flights -> {
  group_by: month_of_year is month(dep_time)
  aggregate: flight_count
  group_by: flight_year is dep_time.year
}
QUERY RESULTS
[
  {
    "month_of_year": 8,
    "flight_count": 6415,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 10,
    "flight_count": 6228,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 7,
    "flight_count": 6227,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 12,
    "flight_count": 6202,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 6,
    "flight_count": 6118,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 9,
    "flight_count": 6100,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 11,
    "flight_count": 6084,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 10,
    "flight_count": 6083,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 5,
    "flight_count": 6064,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 4,
    "flight_count": 6043,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 1,
    "flight_count": 5997,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 8,
    "flight_count": 5993,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 7,
    "flight_count": 5775,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 12,
    "flight_count": 5770,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 3,
    "flight_count": 5732,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 2,
    "flight_count": 5707,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 9,
    "flight_count": 5689,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 6,
    "flight_count": 5622,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 5,
    "flight_count": 5609,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 11,
    "flight_count": 5533,
    "flight_year": "2005-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 3,
    "flight_count": 5463,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 4,
    "flight_count": 5385,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 2,
    "flight_count": 5223,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 12,
    "flight_count": 5134,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 1,
    "flight_count": 5124,
    "flight_year": "2004-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 7,
    "flight_count": 5117,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 5,
    "flight_count": 5114,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 3,
    "flight_count": 5017,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 6,
    "flight_count": 5005,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 8,
    "flight_count": 4918,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 1,
    "flight_count": 4892,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 4,
    "flight_count": 4873,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 11,
    "flight_count": 4729,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 10,
    "flight_count": 4726,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 2,
    "flight_count": 4720,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 8,
    "flight_count": 4607,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 6,
    "flight_count": 4451,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 7,
    "flight_count": 4440,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 9,
    "flight_count": 4431,
    "flight_year": "2003-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 5,
    "flight_count": 4407,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 6,
    "flight_count": 4375,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 5,
    "flight_count": 4348,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 7,
    "flight_count": 4346,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 10,
    "flight_count": 4340,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 4,
    "flight_count": 4328,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 8,
    "flight_count": 4326,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 9,
    "flight_count": 4261,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 3,
    "flight_count": 4252,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 8,
    "flight_count": 4182,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 3,
    "flight_count": 4102,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 12,
    "flight_count": 4086,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 1,
    "flight_count": 4071,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 5,
    "flight_count": 4047,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 4,
    "flight_count": 4046,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 7,
    "flight_count": 4043,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 1,
    "flight_count": 4020,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 1,
    "flight_count": 4008,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 12,
    "flight_count": 3999,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 11,
    "flight_count": 3914,
    "flight_year": "2002-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 2,
    "flight_count": 3876,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 3,
    "flight_count": 3869,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 10,
    "flight_count": 3858,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 9,
    "flight_count": 3850,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 11,
    "flight_count": 3819,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 10,
    "flight_count": 3809,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 4,
    "flight_count": 3778,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 6,
    "flight_count": 3774,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 12,
    "flight_count": 3744,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 11,
    "flight_count": 3718,
    "flight_year": "2000-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 2,
    "flight_count": 3672,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 9,
    "flight_count": 3616,
    "flight_year": "2001-01-01T00:00:00.000Z"
  },
  {
    "month_of_year": 2,
    "flight_count": 3583,
    "flight_year": "2002-01-01T00:00:00.000Z"
  }
]
SELECT 
   EXTRACT(month FROM base."dep_time") as "month_of_year",
   COUNT(1) as "flight_count",
   DATE_TRUNC('year', base."dep_time") as "flight_year"
FROM '../data/flights.parquet' as base
GROUP BY 1,3
ORDER BY 2 desc NULLS LAST

By adding year as the third column, we can display different years on the same chart. Note the # line_chart tag above the query. This is a hint to the renderer to display the data as a line chart. Changing the definition of flight_year to year(dep_time)::string makes the line chart interpret the year as "categorical," giving distinct colors for each year rather than a gradient.

document
# line_chart
run: flights -> {
  group_by: month_of_year is month(dep_time)
  aggregate: flight_count
  group_by: flight_year is year(dep_time)::string
}
QUERY RESULTS
[
  {
    "month_of_year": 8,
    "flight_count": 6415,
    "flight_year": "2005"
  },
  {
    "month_of_year": 10,
    "flight_count": 6228,
    "flight_year": "2004"
  },
  {
    "month_of_year": 7,
    "flight_count": 6227,
    "flight_year": "2005"
  },
  {
    "month_of_year": 12,
    "flight_count": 6202,
    "flight_year": "2004"
  },
  {
    "month_of_year": 6,
    "flight_count": 6118,
    "flight_year": "2005"
  },
  {
    "month_of_year": 9,
    "flight_count": 6100,
    "flight_year": "2005"
  },
  {
    "month_of_year": 11,
    "flight_count": 6084,
    "flight_year": "2004"
  },
  {
    "month_of_year": 10,
    "flight_count": 6083,
    "flight_year": "2005"
  },
  {
    "month_of_year": 5,
    "flight_count": 6064,
    "flight_year": "2005"
  },
  {
    "month_of_year": 4,
    "flight_count": 6043,
    "flight_year": "2005"
  },
  {
    "month_of_year": 1,
    "flight_count": 5997,
    "flight_year": "2005"
  },
  {
    "month_of_year": 8,
    "flight_count": 5993,
    "flight_year": "2004"
  },
  {
    "month_of_year": 7,
    "flight_count": 5775,
    "flight_year": "2004"
  },
  {
    "month_of_year": 12,
    "flight_count": 5770,
    "flight_year": "2005"
  },
  {
    "month_of_year": 3,
    "flight_count": 5732,
    "flight_year": "2005"
  },
  {
    "month_of_year": 2,
    "flight_count": 5707,
    "flight_year": "2005"
  },
  {
    "month_of_year": 9,
    "flight_count": 5689,
    "flight_year": "2004"
  },
  {
    "month_of_year": 6,
    "flight_count": 5622,
    "flight_year": "2004"
  },
  {
    "month_of_year": 5,
    "flight_count": 5609,
    "flight_year": "2004"
  },
  {
    "month_of_year": 11,
    "flight_count": 5533,
    "flight_year": "2005"
  },
  {
    "month_of_year": 3,
    "flight_count": 5463,
    "flight_year": "2004"
  },
  {
    "month_of_year": 4,
    "flight_count": 5385,
    "flight_year": "2004"
  },
  {
    "month_of_year": 2,
    "flight_count": 5223,
    "flight_year": "2004"
  },
  {
    "month_of_year": 12,
    "flight_count": 5134,
    "flight_year": "2003"
  },
  {
    "month_of_year": 1,
    "flight_count": 5124,
    "flight_year": "2004"
  },
  {
    "month_of_year": 7,
    "flight_count": 5117,
    "flight_year": "2003"
  },
  {
    "month_of_year": 5,
    "flight_count": 5114,
    "flight_year": "2003"
  },
  {
    "month_of_year": 3,
    "flight_count": 5017,
    "flight_year": "2003"
  },
  {
    "month_of_year": 6,
    "flight_count": 5005,
    "flight_year": "2003"
  },
  {
    "month_of_year": 8,
    "flight_count": 4918,
    "flight_year": "2003"
  },
  {
    "month_of_year": 1,
    "flight_count": 4892,
    "flight_year": "2003"
  },
  {
    "month_of_year": 4,
    "flight_count": 4873,
    "flight_year": "2003"
  },
  {
    "month_of_year": 11,
    "flight_count": 4729,
    "flight_year": "2003"
  },
  {
    "month_of_year": 10,
    "flight_count": 4726,
    "flight_year": "2003"
  },
  {
    "month_of_year": 2,
    "flight_count": 4720,
    "flight_year": "2003"
  },
  {
    "month_of_year": 8,
    "flight_count": 4607,
    "flight_year": "2001"
  },
  {
    "month_of_year": 6,
    "flight_count": 4451,
    "flight_year": "2001"
  },
  {
    "month_of_year": 7,
    "flight_count": 4440,
    "flight_year": "2001"
  },
  {
    "month_of_year": 9,
    "flight_count": 4431,
    "flight_year": "2003"
  },
  {
    "month_of_year": 5,
    "flight_count": 4407,
    "flight_year": "2001"
  },
  {
    "month_of_year": 6,
    "flight_count": 4375,
    "flight_year": "2002"
  },
  {
    "month_of_year": 5,
    "flight_count": 4348,
    "flight_year": "2002"
  },
  {
    "month_of_year": 7,
    "flight_count": 4346,
    "flight_year": "2002"
  },
  {
    "month_of_year": 10,
    "flight_count": 4340,
    "flight_year": "2002"
  },
  {
    "month_of_year": 4,
    "flight_count": 4328,
    "flight_year": "2001"
  },
  {
    "month_of_year": 8,
    "flight_count": 4326,
    "flight_year": "2002"
  },
  {
    "month_of_year": 9,
    "flight_count": 4261,
    "flight_year": "2002"
  },
  {
    "month_of_year": 3,
    "flight_count": 4252,
    "flight_year": "2000"
  },
  {
    "month_of_year": 8,
    "flight_count": 4182,
    "flight_year": "2000"
  },
  {
    "month_of_year": 3,
    "flight_count": 4102,
    "flight_year": "2002"
  },
  {
    "month_of_year": 12,
    "flight_count": 4086,
    "flight_year": "2001"
  },
  {
    "month_of_year": 1,
    "flight_count": 4071,
    "flight_year": "2001"
  },
  {
    "month_of_year": 5,
    "flight_count": 4047,
    "flight_year": "2000"
  },
  {
    "month_of_year": 4,
    "flight_count": 4046,
    "flight_year": "2002"
  },
  {
    "month_of_year": 7,
    "flight_count": 4043,
    "flight_year": "2000"
  },
  {
    "month_of_year": 1,
    "flight_count": 4020,
    "flight_year": "2000"
  },
  {
    "month_of_year": 1,
    "flight_count": 4008,
    "flight_year": "2002"
  },
  {
    "month_of_year": 12,
    "flight_count": 3999,
    "flight_year": "2002"
  },
  {
    "month_of_year": 11,
    "flight_count": 3914,
    "flight_year": "2002"
  },
  {
    "month_of_year": 2,
    "flight_count": 3876,
    "flight_year": "2000"
  },
  {
    "month_of_year": 3,
    "flight_count": 3869,
    "flight_year": "2001"
  },
  {
    "month_of_year": 10,
    "flight_count": 3858,
    "flight_year": "2000"
  },
  {
    "month_of_year": 9,
    "flight_count": 3850,
    "flight_year": "2000"
  },
  {
    "month_of_year": 11,
    "flight_count": 3819,
    "flight_year": "2001"
  },
  {
    "month_of_year": 10,
    "flight_count": 3809,
    "flight_year": "2001"
  },
  {
    "month_of_year": 4,
    "flight_count": 3778,
    "flight_year": "2000"
  },
  {
    "month_of_year": 6,
    "flight_count": 3774,
    "flight_year": "2000"
  },
  {
    "month_of_year": 12,
    "flight_count": 3744,
    "flight_year": "2000"
  },
  {
    "month_of_year": 11,
    "flight_count": 3718,
    "flight_year": "2000"
  },
  {
    "month_of_year": 2,
    "flight_count": 3672,
    "flight_year": "2001"
  },
  {
    "month_of_year": 9,
    "flight_count": 3616,
    "flight_year": "2001"
  },
  {
    "month_of_year": 2,
    "flight_count": 3583,
    "flight_year": "2002"
  }
]
SELECT 
   EXTRACT(month FROM base."dep_time") as "month_of_year",
   COUNT(1) as "flight_count",
   CAST(EXTRACT(year FROM base."dep_time") AS varchar) as "flight_year"
FROM '../data/flights.parquet' as base
GROUP BY 1,3
ORDER BY 2 desc NULLS LAST

Method 2: Filtered Aggregates

Filters make it easy to reuse aggregate calculations for trends analysis.

document
run: flights -> {
  group_by: carrier
  aggregate:
    flights_in_2002 is flight_count { where: dep_time = @2002 }
    flights_in_2003 is flight_count { where: dep_time = @2003 }
    # percent
    percent_change is 
      (flight_count { where: dep_time = @2003 } - flight_count { where: dep_time = @2002 })
        / nullif(flight_count { where: dep_time = @2003 }, 0)
}
QUERY RESULTS
[
  {
    "carrier": "WN",
    "flights_in_2002": 14708,
    "flights_in_2003": 14300,
    "percent_change": -0.028531468531468533
  },
  {
    "carrier": "US",
    "flights_in_2002": 6501,
    "flights_in_2003": 4321,
    "percent_change": -0.5045128442490164
  },
  {
    "carrier": "AA",
    "flights_in_2002": 5963,
    "flights_in_2003": 5566,
    "percent_change": -0.07132590729428674
  },
  {
    "carrier": "UA",
    "flights_in_2002": 5590,
    "flights_in_2003": 6068,
    "percent_change": 0.07877389584706658
  },
  {
    "carrier": "NW",
    "flights_in_2002": 5081,
    "flights_in_2003": 5187,
    "percent_change": 0.020435704646230964
  },
  {
    "carrier": "DL",
    "flights_in_2002": 4400,
    "flights_in_2003": 5536,
    "percent_change": 0.20520231213872833
  },
  {
    "carrier": "MQ",
    "flights_in_2002": 3469,
    "flights_in_2003": 1341,
    "percent_change": -1.586875466070097
  },
  {
    "carrier": "HP",
    "flights_in_2002": 1586,
    "flights_in_2003": 1609,
    "percent_change": 0.014294592914853946
  },
  {
    "carrier": "AS",
    "flights_in_2002": 1263,
    "flights_in_2003": 1484,
    "percent_change": 0.14892183288409702
  },
  {
    "carrier": "CO",
    "flights_in_2002": 1087,
    "flights_in_2003": 1162,
    "percent_change": 0.06454388984509467
  },
  {
    "carrier": "RU",
    "flights_in_2002": 0,
    "flights_in_2003": 4011,
    "percent_change": 1
  },
  {
    "carrier": "OH",
    "flights_in_2002": 0,
    "flights_in_2003": 0,
    "percent_change": null
  },
  {
    "carrier": "TZ",
    "flights_in_2002": 0,
    "flights_in_2003": 572,
    "percent_change": 1
  },
  {
    "carrier": "EV",
    "flights_in_2002": 0,
    "flights_in_2003": 5989,
    "percent_change": 1
  },
  {
    "carrier": "B6",
    "flights_in_2002": 0,
    "flights_in_2003": 1530,
    "percent_change": 1
  }
]
SELECT 
   base."carrier" as "carrier",
   (COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2002-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2003-01-01 00:00:00') THEN 1 END)) as "flights_in_2002",
   (COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)) as "flights_in_2003",
   ((((COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)))-((COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2002-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2003-01-01 00:00:00') THEN 1 END)))))*1.0/(NULLIF((COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)),0)) as "percent_change"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Method 3: Calculate with Lag

The calculate: clause is Malloy's window function equivalent, and allows us to compute year over year calculations using the lag function:

document
run: flights -> {
  group_by: dep_year is dep_time.year
  aggregate: flight_count
  calculate: 
    last_year is lag(flight_count, 1)
    # percent
    growth is (lag(flight_count, 1) - flight_count) / lag(flight_count, 1)
  order_by: dep_year
}
QUERY RESULTS
[
  {
    "dep_year": "2000-01-01T00:00:00.000Z",
    "flight_count": 47142,
    "last_year": null,
    "growth": null
  },
  {
    "dep_year": "2001-01-01T00:00:00.000Z",
    "flight_count": 49175,
    "last_year": 47142,
    "growth": -0.043125026515633616
  },
  {
    "dep_year": "2002-01-01T00:00:00.000Z",
    "flight_count": 49648,
    "last_year": 49175,
    "growth": -0.00961870869344179
  },
  {
    "dep_year": "2003-01-01T00:00:00.000Z",
    "flight_count": 58676,
    "last_year": 49648,
    "growth": -0.18184015468901063
  },
  {
    "dep_year": "2004-01-01T00:00:00.000Z",
    "flight_count": 68397,
    "last_year": 58676,
    "growth": -0.16567250664666985
  },
  {
    "dep_year": "2005-01-01T00:00:00.000Z",
    "flight_count": 71789,
    "last_year": 68397,
    "growth": -0.04959281839846777
  }
]
SELECT 
   DATE_TRUNC('year', base."dep_time") as "dep_year",
   COUNT(1) as "flight_count",
   LAG((COUNT(1)),1) OVER(  ORDER BY  DATE_TRUNC('year', base."dep_time") ASC NULLS LAST ) as "last_year",
   (((LAG((COUNT(1)),1) OVER(  ORDER BY  DATE_TRUNC('year', base."dep_time") ASC NULLS LAST ))-(COUNT(1))))*1.0/(LAG((COUNT(1)),1) OVER(  ORDER BY  DATE_TRUNC('year', base."dep_time") ASC NULLS LAST )) as "growth"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 1 ASC NULLS LAST

Bonus: Relative timeframes and expression reuse

You might like to write queries that automatically adjust based on the current timeframe. The query below uses date arithmetic to filter the data to time frames relative to now. These measures probably aren't generally useful in the model so we use the extend: operation to add these measure so they are only locally accessable within the query.

document
source: inventory_items is duckdb.table('../data/inventory_items.parquet') 

source: order_items is duckdb.table('../data/order_items.parquet') extend {
  join_one: inventory_items  on inventory_item_id = inventory_items.id
  measure: order_item_count is count()

  view: category_growth is {
    extend: {
      // add measures for use just in this query
      measure:
        last_year is order_item_count { where: created_at ? now.year - 1 year }
        prior_year is order_item_count { where: created_at ? now.year - 2 years }
    }
    limit: 10
    group_by: inventory_items.product_category
    aggregate:
      last_year
      prior_year
      # percent
      percent_change is (last_year - prior_year) / nullif(last_year, 0)
  }
}

run: order_items -> category_growth
QUERY RESULTS
[
  {
    "product_category": "Sleep & Lounge",
    "last_year": 0,
    "prior_year": 3043,
    "percent_change": null
  },
  {
    "product_category": "Sweaters",
    "last_year": 0,
    "prior_year": 4147,
    "percent_change": null
  },
  {
    "product_category": "Tops & Tees",
    "last_year": 0,
    "prior_year": 10027,
    "percent_change": null
  },
  {
    "product_category": "Blazers & Jackets",
    "last_year": 0,
    "prior_year": 2269,
    "percent_change": null
  },
  {
    "product_category": "Shorts",
    "last_year": 0,
    "prior_year": 7536,
    "percent_change": null
  }
]
SELECT 
   inventory_items_0."product_category" as "product_category",
   (COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year) + INTERVAL (1) year)) THEN 1 END)) as "last_year",
   (COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year) + INTERVAL (1) year)) THEN 1 END)) as "prior_year",
   ((((COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year) + INTERVAL (1) year)) THEN 1 END)))-((COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year) + INTERVAL (1) year)) THEN 1 END)))))*1.0/(NULLIF(((COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year) + INTERVAL (1) year)) THEN 1 END))),0)) as "percent_change"
FROM '../data/order_items.parquet' as base
 LEFT JOIN '../data/inventory_items.parquet' AS inventory_items_0
  ON base."inventory_item_id"=inventory_items_0."id"
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 10