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_​yearflight_​countflight_​year
86,4152005
106,2282004
76,2272005
126,2022004
66,1182005
96,1002005
116,0842004
106,0832005
56,0642005
46,0432005
15,9972005
85,9932004
75,7752004
125,7702005
35,7322005
25,7072005
95,6892004
65,6222004
55,6092004
115,5332005
35,4632004
45,3852004
25,2232004
125,1342003
15,1242004
75,1172003
55,1142003
35,0172003
65,0052003
84,9182003
14,8922003
44,8732003
114,7292003
104,7262003
24,7202003
84,6072001
64,4512001
74,4402001
94,4312003
54,4072001
64,3752002
54,3482002
74,3462002
104,3402002
44,3282001
84,3262002
94,2612002
34,2522000
84,1822000
34,1022002
124,0862001
14,0712001
54,0472000
44,0462002
74,0432000
14,0202000
14,0082002
123,9992002
113,9142002
23,8762000
33,8692001
103,8582000
93,8502000
113,8192001
103,8092001
43,7782000
63,7742000
123,7442000
113,7182000
23,6722001
93,6162001
23,5832002
[
  {
    "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 flights."dep_time") as "month_of_year",
   COUNT( 1) as "flight_count",
   DATE_TRUNC('year', flights."dep_time") as "flight_year"
FROM '../data/flights.parquet' as flights
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
123456789101112month_of_year3,0003,5004,0004,5005,0005,5006,0006,5007,000flight_count200020012002200320042005flight_year
[
  {
    "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 flights."dep_time") as "month_of_year",
   COUNT( 1) as "flight_count",
   CAST(EXTRACT(year FROM flights."dep_time") AS varchar) as "flight_year"
FROM '../data/flights.parquet' as flights
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
carrierflights_​in_​2002flights_​in_​2003percent_​change
WN14,70814,300-2.85%
US6,5014,321-50.45%
AA5,9635,566-7.13%
UA5,5906,0687.88%
NW5,0815,1872.04%
DL4,4005,53620.52%
MQ3,4691,341-158.69%
HP1,5861,6091.43%
AS1,2631,48414.89%
CO1,0871,1626.45%
EV05,989100%
TZ0572100%
B601,530100%
RU04,011100%
OH00
[
  {
    "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": "EV",
    "flights_in_2002": 0,
    "flights_in_2003": 5989,
    "percent_change": 1
  },
  {
    "carrier": "TZ",
    "flights_in_2002": 0,
    "flights_in_2003": 572,
    "percent_change": 1
  },
  {
    "carrier": "B6",
    "flights_in_2002": 0,
    "flights_in_2003": 1530,
    "percent_change": 1
  },
  {
    "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
  }
]
SELECT 
   flights."carrier" as "carrier",
   (COUNT( CASE WHEN (flights."dep_time">=TIMESTAMP '2002-01-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2003-01-01 00:00:00') THEN 1 END)) as "flights_in_2002",
   (COUNT( CASE WHEN (flights."dep_time">=TIMESTAMP '2003-01-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)) as "flights_in_2003",
   ((COUNT( CASE WHEN (flights."dep_time">=TIMESTAMP '2003-01-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END))-(COUNT( CASE WHEN (flights."dep_time">=TIMESTAMP '2002-01-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2003-01-01 00:00:00') THEN 1 END)))*1.0/NULLIF((COUNT( CASE WHEN (flights."dep_time">=TIMESTAMP '2003-01-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)), 0) as "percent_change"
FROM '../data/flights.parquet' as flights
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_​yearflight_​countlast_​yeargrowth
200047,142
200149,17547,142-4.31%
200249,64849,175-0.96%
200358,67649,648-18.18%
200468,39758,676-16.57%
200571,78968,397-4.96%
[
  {
    "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', flights."dep_time") as "dep_year",
   COUNT( 1) as "flight_count",
   LAG((COUNT( 1)), 1) OVER(  ORDER BY  DATE_TRUNC('year', flights."dep_time") ASC NULLS LAST ) as "last_year",
   (LAG((COUNT( 1)), 1) OVER(  ORDER BY  DATE_TRUNC('year', flights."dep_time") ASC NULLS LAST )-(COUNT( 1)))*1.0/LAG((COUNT( 1)), 1) OVER(  ORDER BY  DATE_TRUNC('year', flights."dep_time") ASC NULLS LAST ) as "growth"
FROM '../data/flights.parquet' as flights
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_​categorylast_​yearprior_​yearpercent_​change
Leggings01,528
Jeans012,053
Swim03,353
Accessories012,713
Dresses04,461
[
  {
    "product_category": "Leggings",
    "last_year": 0,
    "prior_year": 1528,
    "percent_change": null
  },
  {
    "product_category": "Jeans",
    "last_year": 0,
    "prior_year": 12053,
    "percent_change": null
  },
  {
    "product_category": "Swim",
    "last_year": 0,
    "prior_year": 3353,
    "percent_change": null
  },
  {
    "product_category": "Accessories",
    "last_year": 0,
    "prior_year": 12713,
    "percent_change": null
  },
  {
    "product_category": "Dresses",
    "last_year": 0,
    "prior_year": 4461,
    "percent_change": null
  }
]
SELECT 
   inventory_items_0."product_category" as "product_category",
   (COUNT( CASE WHEN (order_items."created_at">=DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)and(order_items."created_at"<DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year + INTERVAL (1) year) THEN 1 END)) as "last_year",
   (COUNT( CASE WHEN (order_items."created_at">=DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year)and(order_items."created_at"<DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year + INTERVAL (1) year) THEN 1 END)) as "prior_year",
   (((COUNT( CASE WHEN (order_items."created_at">=DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)and(order_items."created_at"<DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year + INTERVAL (1) year) THEN 1 END)))-((COUNT( CASE WHEN (order_items."created_at">=DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year)and(order_items."created_at"<DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year + INTERVAL (1) year) THEN 1 END))))*1.0/NULLIF(((COUNT( CASE WHEN (order_items."created_at">=DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)and(order_items."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 order_items
 LEFT JOIN '../data/inventory_items.parquet' AS inventory_items_0
  ON order_items."inventory_item_id"=inventory_items_0."id"
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 10