• Pull Request
  • Play with the query and results using the Fiddle. Unfortunately, the Fiddle uses a downsampled dataset, and Query 4 doesn’t return any results on it.

Query 4 is a headscratcher. The motivating business question is only moderately complex, but the SQL implementation is needlessly complicated. The Malloy version is a LOT simpler, but to be fair to SQL, I’m pretty sure I could rewrite the SQL to be much easier to understand.

The query returns a list of customers from 2002 whose year-over-year growth in catalog sales exceeds their year-over-year growth in both either store and web sales. The query unions the sales tables from each of catalog, web, and store, and then does a six-way self-join to compute the year-over-year values.

The Malloy model for this query is pretty straightforward. Similar to the approach in query 2, I’m using a SQL Block to construct a Source from a union of the catalog_sales, web_sales and store_sales tables, and joining in the appropriate dimension tables.

The query itself is also quite simple. Rather than doing a six-way self-join like the SQL implementation, I use filtered aggregations to calculate sales for each year, then pipeline the results (using the -> operator) to divide 2002 sales by 2001 sales.

The declare clause in the query above uses a construct that doesn’t exist in SQL. This clause allows me to create new variables in the query scope that can be re-used elsewhere. In a SQL query, variable declarations take the form of aliased columns in the SELECT clause; however, it’s fairly common to want to use an intermediate variable in a query, but exclude it from the final result – for example, when computing row ranks with a window function and selecting the first row of that window. Unfortunately, on most databases, this requires tacking on an additional CTE to exclude the rank column from the output resultset. With declare in Malloy, I define the variables and use them in the query’s filters, but don’t include them anywhere in the final output. Pretty slick!

Thanks for tuning in! As always, any feedback, comments, or questions are welcome. Drop me a line on the Malloy Slack, username @carl.