• Mustache Interpolation
  • Variable substitution
  • Mustache sections
  • Haskell splicing
    • String interpolation
    • Select clause interpolation

Mustache Interpolation

Mustache templates interpolation is the core feature of Napkin. It is used by Napkin internally (for incremental queries) and can be utilized by users to achieve some variability in the queries, which Napkin will execute on the database.

Every SQL file or query Napkin tries to parse is treated as a mustache template, where things like {{{variable}}} will be replaced with the value of the variable.

The typical use cases for variable interpolation are:

• query deduplication – reusable query can be stored in a SQL file and used with different variable values to create multiple tables,
• customizing queries by passing arguments via CLI (--arg) options.

Let’s demonstrate the concept with a single example: we want to define a query, which will create artist_of_the_month view. The table should contain only artists with names starting from the first letter of the current month’s name.

Variable substitution

We start from defining the idea in the spec.yaml file:

specs/spec.yaml

db_url: sqlite:data/chinook-sql.db
backend: Sqlite
tables:
  artist_of_the_month:
    create_action:
      sql_query:
        query: SELECT * FROM Artist WHERE Name LIKE upper('{{{letter}}}') || '%'
        vars:
          letter: j # (stands for January)

Running such Napkin spec creates a desired view:

shell

napkin run --spec-file specs/spec.yaml --verbose

output

[2022-01-12 08:10:27][Info] Determining tables for update...
[2022-01-12 08:10:27][Debug] Tables in execution plan:
artist_of_the_month: Execute / UpdateStrategy UpdateAlways

[2022-01-12 08:10:27][Info] Forced tables: none
[2022-01-12 08:10:27][Info] Skipped tables: none
[2022-01-12 08:10:27][Info] Unmanaged tables that will be used as an input in this run:
- Artist

[2022-01-12 08:10:27][Info] Managed tables that will be used as an input in this run, but will not be updated: none
[2022-01-12 08:10:27][Info] Managed tables that will be updated in this run:
- artist_of_the_month

[2022-01-12 08:10:27][Info] Estimated runtime: 0s
[2022-01-12 08:10:27][Info] Running table hooks (Pre)
[2022-01-12 08:10:27][Info] Executing table action.
[2022-01-12 08:10:27][Debug] Executing command: DROP VIEW IF EXISTS "artist_of_the_month"
[2022-01-12 08:10:27][Debug] Command performed in 0.00.
[2022-01-12 08:10:27][Debug] Executing command: CREATE VIEW "artist_of_the_month" AS
SELECT *
FROM "Artist" AS "Artist"
WHERE ("Name" like (upper('j') || '%'))
[2022-01-12 08:10:27][Debug] Command performed in 0.00.
[2022-01-12 08:10:27][Info] Table action complete.
[2022-01-12 08:10:27][Info] Running table hooks (Post)
[2022-01-12 08:10:27][Info] Table processing complete.
[2022-01-12 08:10:27][Info] TableSpec "artist_of_the_month" server stats: unknown
[2022-01-12 08:10:27][Info] Execution completed. Please see below for a summary.
[2022-01-12 08:10:27][Info] ----------------------------------------------------------
[2022-01-12 08:10:27][Info] Table "artist_of_the_month" ran in 0.00 Server stats: unknown
[2022-01-12 08:10:27][Info] Run complete. Total cost: unknown

This feature is useful for cases when you have to define multiple very similar tables. Let’s imagine we need a single view per month.

First, we move query to the single sql/artist_per_month.sql file:

sql/artist_per_month.sql

SELECT * FROM Artist WHERE Name LIKE upper('{{{letter}}}') || '%'

And the spec.yaml spec would look like that:

specs/spec.yaml

db_url: sqlite:data/chinook-sql.db
sql_folder: ../sql
backend: Sqlite
tables:
  artist_for_january:
    create_action:
      sql_file:
        source: artist_per_month.sql
        vars: { letter: j }

  artist_for_february:
    create_action:
      sql_file:
        source: artist_per_month.sql
        vars: { letter: f }

  artist_for_march:
    create_action:
      sql_file:
        source: artist_per_month.sql
        vars: { letter: m }

Yes, this is still repetitive table definitions (see Haskell API section to know how to avoid that), but at least the query itself is only defined once.

But this is not very interesting, hard-coding a letter whenever next month coming isn’t fun. Lets try to provide a variable name from the outside of the spec.yaml file.

First, lets try to remove the variable definition from the spec, leaving it undefined:

specs/spec.yaml

db_url: sqlite:data/chinook-sql.db
backend: Sqlite
tables:
  artist_of_the_month:
    create_action:
      sql_query:
        query: SELECT * FROM Artist WHERE Name LIKE upper('{{{letter}}}') || '%'

Napkin tries to protect from users from accidental mistakes, which are possible from following the mustache interpolation semantics (which treats non defined variables as empty). Running validate command with additional --strict-mustache flag does detects a problem with undefined variable:

shell

napkin validate --spec-file specs/spec.yaml --strict-mustache

output

Spec error artist_of_the_month: Could not load inline sql: Failed to process mustache template: Template Engine Error: in `SELECT * FROM Artist WHERE Name LIKE upper('{{{letter}}}'...` with a message: Interpolation warning: Referenced value was not provided, key: letter

Fortunately, there is a way out! Napkin propagates spec arguments into mustache variables under the "args". So we can override values by providing command line arguments.

Before doing that, we should change spec.yaml definition slightly, pre-pending {{{letter}}} variable with args prefix.

specs/spec.yaml

db_url: sqlite:data/chinook-sql.db
backend: Sqlite
tables:
  artist_of_the_month:
    create_action:
      sql_query:
        query: SELECT * FROM Artist WHERE Name LIKE upper('{{{args.letter}}}') || '%'

Now, after providing an override from CLI, Napkin creates a correct view:

shell

napkin run --spec-file specs/spec.yaml --verbose --arg letter=f # stands for February

output

[2022-01-12 09:10:23][Info] Determining tables for update...
[2022-01-12 09:10:23][Debug] Tables in execution plan:
artist_of_the_month: Execute / UpdateStrategy UpdateAlways

[2022-01-12 09:10:23][Info] Forced tables: none
[2022-01-12 09:10:23][Info] Skipped tables: none
[2022-01-12 09:10:23][Info] Unmanaged tables that will be used as an input in this run:
- Artist

[2022-01-12 09:10:23][Info] Managed tables that will be used as an input in this run, but will not be updated: none
[2022-01-12 09:10:23][Info] Managed tables that will be updated in this run:
- artist_of_the_month

[2022-01-12 09:10:23][Info] Estimated runtime: 0s
[2022-01-12 09:10:23][Info] Running table hooks (Pre)
[2022-01-12 09:10:23][Info] Executing table action.
[2022-01-12 09:10:23][Debug] Executing command: DROP VIEW IF EXISTS "artist_of_the_month"
[2022-01-12 09:10:23][Debug] Command performed in 0.00.
[2022-01-12 09:10:23][Debug] Executing command: CREATE VIEW "artist_of_the_month" AS
SELECT *
FROM "Artist" AS "Artist"
WHERE ("Name" like (upper('f') || '%'))
[2022-01-12 09:10:23][Debug] Command performed in 0.00.
[2022-01-12 09:10:23][Info] Table action complete.
[2022-01-12 09:10:23][Info] Running table hooks (Post)
[2022-01-12 09:10:23][Info] Table processing complete.
[2022-01-12 09:10:23][Info] TableSpec "artist_of_the_month" server stats: unknown
[2022-01-12 09:10:23][Info] Execution completed. Please see below for a summary.
[2022-01-12 09:10:23][Info] ----------------------------------------------------------
[2022-01-12 09:10:23][Info] Table "artist_of_the_month" ran in 0.00 Server stats: unknown
[2022-01-12 09:10:23][Info] Run complete. Total cost: unknown

Mustache sections

But what if the context of the task has suddenly changed and now we need to filter artists with names, starting with several different letters? Napkin to the rescue

Mustache supports named sections syntax, which behaves differently, depending on the variable value:

• Section is not rendered if condition variable is not defined or ‘false’ or ‘empty list’.
• Section is rendered multiple times for ‘non-empty list’, binding list element as a variable set.
• Section is rendered once for a non-empty value, binding it as a variable set.

Lets define a query in spec.yaml, which utilized a section syntax and performs our changed task:

specs/spec.yaml

db_url: sqlite:data/chinook-sql.db
backend: Sqlite
tables:
  artist_of_the_month:
    create_action:
      sql_query:
        target_type: view
        query: |
          SELECT * FROM Artist WHERE
            False
            {{#args.letters}}
              OR Name LIKE upper('{{{letter}}}') || '%'
            {{/args.letters}}

With strict mustache evaluation mode, Napkin produces an error when variable mentioned in section name is not defined:

shell

napkin validate --spec-file specs/spec.yaml --strict-mustache

output

Spec error artist_of_the_month: Could not load inline sql: Failed to process mustache template: Template Substitution Error: in `SELECT * FROM Artist WHERE False {{^args.letters}} OR Name...` with a message: Variable used in section declaration is not defined: letters

Lets specify the values with --arg-json CLI flag:

shell

napkin run --spec-file specs/spec.yaml --arg-json '{"letters": [{"letter": "a"}, {"letter": "b"}]}' --verbose

output

[2022-01-12 18:16:51][Info] Determining tables for update...
[2022-01-12 18:16:51][Debug] Tables in execution plan:
artist_of_the_month: Execute / UpdateStrategy UpdateAlways

[2022-01-12 18:16:51][Info] Forced tables: none
[2022-01-12 18:16:51][Info] Skipped tables: none
[2022-01-12 18:16:51][Info] Unmanaged tables that will be used as an input in this run:
- Artist

[2022-01-12 18:16:51][Info] Managed tables that will be used as an input in this run, but will not be updated: none
[2022-01-12 18:16:51][Info] Managed tables that will be updated in this run:
- artist_of_the_month

[2022-01-12 18:16:51][Info] Estimated runtime: 0s
[2022-01-12 18:16:51][Info] Running table hooks (Pre)
[2022-01-12 18:16:51][Info] Executing table action.
[2022-01-12 18:16:51][Debug] Executing command: DROP VIEW IF EXISTS "artist_of_the_month"
[2022-01-12 18:16:51][Debug] Command performed in 0.00.
[2022-01-12 18:16:51][Debug] Executing command: CREATE VIEW "artist_of_the_month" AS
SELECT *
FROM "Artist" AS "Artist"
WHERE ((false) or (("Name" like (upper('a') || '%')))) or (("Name" like (upper('b') || '%')))
[2022-01-12 18:16:51][Debug] Command performed in 0.00.
[2022-01-12 18:16:51][Info] Table action complete.
[2022-01-12 18:16:51][Info] Running table hooks (Post)
[2022-01-12 18:16:51][Info] Table processing complete.
[2022-01-12 18:16:51][Info] TableSpec "artist_of_the_month" server stats: unknown
[2022-01-12 18:16:51][Info] Execution completed. Please see below for a summary.
[2022-01-12 18:16:51][Info] ----------------------------------------------------------
[2022-01-12 18:16:51][Info] Table "artist_of_the_month" ran in 0.01 Server stats: unknown
[2022-01-12 18:16:51][Info] Run complete. Total cost: unknown

This time Napkin replaces both letters and executed the correct resulting query. But there is a better way to inspect the query without asking Napkin to execute it – the dump command.

Let’s change out query one more time, to insert a default behavior (when no letter is specified). We will use the mustache inverse section, which is executed when the expression inside it is ‘false’ or ‘empty’.

specs/spec.yaml

db_url: sqlite:data/chinook-sql.db
backend: Sqlite
tables:
  artist_of_the_month:
    create_action:
      sql_query:
        target_type: view
        query: |
          SELECT * FROM Artist WHERE
            False
            {{^args.letters}}
              OR Name = 'Metallica'
            {{/args.letters}}
            {{#args.letters}}
              OR Name LIKE upper('{{{letter}}}') || '%'
            {{/args.letters}}

Dumping the query gives us different results depending on provided arguments:

shell

napkin dump --spec-file specs/spec.yaml

output

SELECT *
FROM "Artist" AS "Artist"
WHERE (false) or (("Name" = 'Metallica'))

SELECT *
FROM "Artist" AS "Artist"
WHERE (false) or (("Name" like (upper('b') || '%')))

Haskell splicing

Napkin has number of special mustache sections, which are evaluated in non-standard mustache way.

In scope of this tutorial, we will cover only two: strExp and selExp, whereas others can be found in haddock documentation.

String interpolation

strExp is the simplest one of all – inner contents of the section gets evaluated as Haskell code. The resulting string will be used as a substitution for a section body.

Lets say you have a table with such structure:

The task is to calculate minimum and maximum week sales amount for each quarter.

Obvious solution would be to such SQL query:

query.sql

SELECT
  year,
  GREATEST(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) AS q1_greatest,
  LEAST(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) AS q1_least,
  GREATEST(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) AS q2_greatest,
  LEAST(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) AS q2_least,
  GREATEST(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) AS q3_greatest,
  LEAST(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) AS q3_least,
  GREATEST(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) AS q4_greatest,
  LEAST(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) AS q4_least
FROM sales
ORDER BY year ASC

But this is very tedious and error prone to write and modify “by hands”. Instead, we are going to generate part of the query with mustache interpolation:

specs/spec.yaml

tables:
  result:
    create_action:
      sql_query:
        query: |
          SELECT
            {{#quarters}}
              {{#functions}}
                {{{function}}}(q{{{q}}}m1w1, q{{{q}}}m1w2, q{{{q}}}m1w3, q{{{q}}}m1w4, q{{{q}}}m2w1, q{{{q}}}m2w2, q{{{q}}}m2w3, q{{{q}}}m2w4, q{{{q}}}m3w1, q{{{q}}}m3w2, q{{{q}}}m3w3, q{{{q}}}m3w4) as q{{{q}}}_{{{function}}},
              {{/functions}}
            {{/quarters}}
            year FROM sales ORDER BY year ASC
        vars:
          functions:
            - function: greatest
            - function: least
          quarters:
            - q: 1
            - q: 2
            - q: 3
            - q: 4
    post_hooks:
      - assert_expression:
          expression: (q1_greatest >= q1_least)
      - assert_expression:
          expression: (q2_greatest >= q2_least)
      - assert_expression:
          expression: (q3_greatest >= q3_least)
      - assert_expression:
          expression: (q4_greatest >= q4_least)

As expected, this query, being rendered, looks exactly like we need:

output

SELECT
  greatest(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) AS q1_greatest,
  least(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) AS q1_least,
  greatest(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) AS q2_greatest,
  least(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) AS q2_least,
  greatest(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) AS q3_greatest,
  least(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) AS q3_least,
  greatest(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) AS q4_greatest,
  least(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) AS q4_least,
  year
FROM sales AS sales
ORDER BY year ASC

But, that is still not very pleasant to maintain. It is too easy to make an accidental mistake in the long expression crafted by hands. Haskell to the rescue

We’ll write a Haskell expression, which generates exactly everything for us:

GHCI

:{
intercalate ", \n" $ join $ for [1..4] $ \quarter -> for ["greatest", "least"] $ \fun ->
  let expr = intercalate ", " $ join $ for [1..3] $ \month -> for [1..4] $ \week -> "q" <> show quarter <> "m" <> show month <> "w" <> show week
  in fun <> "(" <> expr <> ") as q" <> show quarter <> "_" <> fun
:}

output

greatest(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) as q1_greatest,
least(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) as q1_least,
greatest(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) as q2_greatest,
least(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) as q2_least,
greatest(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) as q3_greatest,
least(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) as q3_least,
greatest(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) as q4_greatest,
least(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) as q4_least

In order to embed the evaluation result into the SQL query, we would need to use special {{#strExp}}...{{/strExp}} section:

specs/spec.yaml

tables:
  result:
    create_action:
      sql_query:
        query: |
          SELECT year, {{#strExp}}
          intercalate ", \n" $ join $ for [1..4] $ \quarter -> for ["greatest", "least"] $ \fun ->
            let expr = intercalate ", " $ join $ for [1..3] $ \month -> for [1..4] $ \week -> "q" <> show quarter <> "m" <> show month <> "w" <> show week
            in fun <> "(" <> expr <> ") as q" <> show quarter <> "_" <> fun
          {{/strExp}} FROM sales ORDER BY year ASC
    post_hooks:
      - assert_expression:
          expression: (q1_greatest >= q1_least)
      - assert_expression:
          expression: (q2_greatest >= q2_least)
      - assert_expression:
          expression: (q3_greatest >= q3_least)
      - assert_expression:
          expression: (q4_greatest >= q4_least)

post_hooks is here just to demonstrate the idea of a posteriori data verification: column with greatest value cannot be less that a column with least value.

It is very hard to make an accidental mistake in the SQL statement you generate. If, in future, we would need to also calculate the SUM of the sales in each quarter, it would be easy to refactor the Haskell expression as such:

GHCI

:{
intercalate ", \n" $ join $ for [1..4] $ \quarter ->
  let expr = join $ for [1..3] $ \month -> for [1..4] $ \week -> "q" <> show quarter <> "m" <> show month <> "w" <> show week
      vals = for ["greatest", "least"] $ \fun -> fun <> "(" <> intercalate ", " expr <> ") as q" <> show quarter <> "_" <> fun
  in [intercalate " + " expr <> " as q" <> show quarter <> "_sum"] <> vals
:}

output

q1m1w1 + q1m1w2 + q1m1w3 + q1m1w4 + q1m2w1 + q1m2w2 + q1m2w3 + q1m2w4 + q1m3w1 + q1m3w2 + q1m3w3 + q1m3w4 as q1_sum,
greatest(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) as q1_greatest,
least(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) as q1_least,
q2m1w1 + q2m1w2 + q2m1w3 + q2m1w4 + q2m2w1 + q2m2w2 + q2m2w3 + q2m2w4 + q2m3w1 + q2m3w2 + q2m3w3 + q2m3w4 as q2_sum,
greatest(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) as q2_greatest,
least(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) as q2_least,
q3m1w1 + q3m1w2 + q3m1w3 + q3m1w4 + q3m2w1 + q3m2w2 + q3m2w3 + q3m2w4 + q3m3w1 + q3m3w2 + q3m3w3 + q3m3w4 as q3_sum,
greatest(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) as q3_greatest,
least(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) as q3_least,
q4m1w1 + q4m1w2 + q4m1w3 + q4m1w4 + q4m2w1 + q4m2w2 + q4m2w3 + q4m2w4 + q4m3w1 + q4m3w2 + q4m3w3 + q4m3w4 as q4_sum,
greatest(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) as q4_greatest,
least(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) as q4_least

And voila, we have a result we wanted:

Select clause interpolation

The same result can be achieved with selExp special mustache section, but in more type safe way. Instead of generating a string, we will generate part of the SQL statement in AST form (string conversion is implicit).

Resulting query will be slightly different, but semantically remains the same:

specs/spec.yaml

tables:
  result:
    create_action:
      sql_query:
        query: |
          SELECT year, {{#selExp}}
            join $ for [1..4] $ \quarterNumber ->
              let quarter = "q" <> show quarterNumber
                  lst = [varString $ quarter <> "m" <> show month <> "w" <> show week | month <- [1..3], week <- [1..4]]
              in [
                greatest lst `as` ref (quarter <> "_greatest"),
                least lst `as` ref (quarter <> "_least"),
                foldl1 (+) lst `as` ref (quarter <> "_sum")
              ]
          {{/selExp}} FROM sales ORDER BY year ASC
    post_hooks:
      - assert_expression:
          expression: (q1_greatest >= q1_least)
      - assert_expression:
          expression: (q2_greatest >= q2_least)
      - assert_expression:
          expression: (q3_greatest >= q3_least)
      - assert_expression:
          expression: (q4_greatest >= q4_least)

output

SELECT
  year,
  greatest(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) AS q1_greatest,
  least(q1m1w1, q1m1w2, q1m1w3, q1m1w4, q1m2w1, q1m2w2, q1m2w3, q1m2w4, q1m3w1, q1m3w2, q1m3w3, q1m3w4) AS q1_least,
  (((((((((((q1m1w1 + q1m1w2) + q1m1w3) + q1m1w4) + q1m2w1) + q1m2w2) + q1m2w3) + q1m2w4) + q1m3w1) + q1m3w2) + q1m3w3) + q1m3w4) AS q1_sum,
  greatest(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) AS q2_greatest,
  least(q2m1w1, q2m1w2, q2m1w3, q2m1w4, q2m2w1, q2m2w2, q2m2w3, q2m2w4, q2m3w1, q2m3w2, q2m3w3, q2m3w4) AS q2_least,
  (((((((((((q2m1w1 + q2m1w2) + q2m1w3) + q2m1w4) + q2m2w1) + q2m2w2) + q2m2w3) + q2m2w4) + q2m3w1) + q2m3w2) + q2m3w3) + q2m3w4) AS q2_sum,
  greatest(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) AS q3_greatest,
  least(q3m1w1, q3m1w2, q3m1w3, q3m1w4, q3m2w1, q3m2w2, q3m2w3, q3m2w4, q3m3w1, q3m3w2, q3m3w3, q3m3w4) AS q3_least,
  (((((((((((q3m1w1 + q3m1w2) + q3m1w3) + q3m1w4) + q3m2w1) + q3m2w2) + q3m2w3) + q3m2w4) + q3m3w1) + q3m3w2) + q3m3w3) + q3m3w4) AS q3_sum,
  greatest(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) AS q4_greatest,
  least(q4m1w1, q4m1w2, q4m1w3, q4m1w4, q4m2w1, q4m2w2, q4m2w3, q4m2w4, q4m3w1, q4m3w2, q4m3w3, q4m3w4) AS q4_least,
  (((((((((((q4m1w1 + q4m1w2) + q4m1w3) + q4m1w4) + q4m2w1) + q4m2w2) + q4m2w3) + q4m2w4) + q4m3w1) + q4m3w2) + q4m3w3) + q4m3w4) AS q4_sum
FROM sales AS sales
ORDER BY year ASC