11 Writing practice

Reading back a sentence and producing one are different skills, so which one do you actually have? Recognition came free with the chapters behind you. Recall is the day-two promise, the one you will use every working day. The only way to get it is the way this chapter is built: you write, look, and refine.

The method is always the same. Start from the smallest legal sentence, which is the table’s name and one verb. Look at what came back. Add one word or one step, and look again. A pipeline is a plan, so every intermediate look is free, and nobody who works this way needs to hold a whole sentence in their head before typing.

11.1 First question: what is slow-moving stock?

Say the products that sold fewer than fifteen units all year. Start smaller than the question: totals per product.

run('
  sales
    then summarize [sold] as total([quantity]) by [product]
')

product	sold
Doohickey	10
Gadget	12
Sprocket	16
Widget	28

sales |> summarize(sold = total(quantity), by = product)

product	sold
Doohickey	10
Gadget	12
Sprocket	16
Widget	28

sales >> summarize(sold = total(col.quantity), by = col.product)

product	sold
Doohickey	10
Gadget	12
Sprocket	16
Widget	28

sales then summarize [sold] as total([quantity]) by [product]

That is most of the answer already. One keep finishes it:

run('
  sales
    then summarize [sold] as total([quantity]) by [product]
    then keep where ([sold] < 15)
')

product	sold
Doohickey	10
Gadget	12

sales |> summarize(sold = total(quantity), by = product) |> keep(sold < 15)

product	sold
Doohickey	10
Gadget	12

(sales
  >> summarize(sold = total(col.quantity), by = col.product)
  >> keep(col.sold < 15))

product	sold
Doohickey	10
Gadget	12

sales then summarize [sold] as total([quantity]) by [product] then keep where [sold] < 15

Note where the keep went: after the summarize, asking about the new sold column. A keep before it would have asked a different question, about single orders rather than about products.

11.2 Second question: where does the margin come from?

Say each region’s total margin, largest first. Build it in the order you would explain it: make the margin, collapse it, order the answer.

run('
  sales
    then add [margin] as ([revenue] - [cost])
    then summarize [margin] as total([margin]) by [region]
    then sort [margin] descending
')

region	margin
West	328
East	285
North	245

sales |>
  add(margin = revenue - cost) |>
  summarize(margin = total(margin), by = region) |>
  sort(descending(margin))

region	margin
West	328
East	285
North	245

(sales
  >> add(margin = col.revenue - col.cost)
  >> summarize(margin = total(col.margin), by = col.region)
  >> sort(descending(col.margin)))

region	margin
West	328
East	285
North	245

sales then add [margin] as [revenue] - [cost] then summarize [margin] as total([margin]) by [region] then sort [margin] descending

11.3 Third question: who is the strongest respondent?

From the survey, read the top three people by the first question’s score. You have not seen this question, and you own every word it needs.

run('
  survey
    then pick [name, region, q1_score]
    then sort [q1_score] descending, [name]
    then take 3
')

name	region	q1_score
ben	East	5
fay	North	5
ana	West	4

survey |>
  pick(name, region, q1_score) |>
  sort(descending(q1_score), name) |>
  take(3)

name	region	q1_score
ben	East	5
fay	North	5
ana	West	4

(survey
  >> pick(col.name, col.region, col.q1_score)
  >> sort(descending(col.q1_score), col.name)
  >> take(3))

name	region	q1_score
ben	East	5
fay	North	5
ana	West	4

survey then pick [name, region, q1_score] then sort [q1_score] descending, [name] then take 3

The second sort key is doing quiet work: two respondents share a score at the cut, and without a tiebreak “top three” would be leaving the choice to chance. Naming the tiebreak is naming the choice.

One limitation is in view: this sentence reads one score column, and the survey has eight. The strongest respondent across all eight, without naming them one by one, is a question this part cannot say yet. Columns into rows can, in one sentence, by turning the eight into one column a largest can read. Knowing exactly where your current words stop is part of owning them.

11.4 When the engine says no

Writing means meeting refusals, so meet four on purpose. Each is the grammar keeping a promise from an earlier chapter, and each message carries its own repair.

A column that a step removed is gone for every later step, because each step sees only the table the last one made:

sales |>
  summarize(t = total(revenue), by = region) |>
  keep(cost > 100) |>
  collect()

Error:
! 
illegal: there is no column called `cost`. The table has: region, t
  |
3 |   then keep where ([cost] > 100)
  |                     ^^^^

try:
    collect(sales
      >> summarize(t = total(col.revenue), by = col.region)
      >> keep(col.cost > 100))
except GodError as refusal:
    print(refusal)


illegal: there is no column called `cost`. The table has: region, t
  |
3 |   then keep where ([cost] > 100)
  |                     ^^^^

The message lists what the table holds at that step: two columns, not six. Read the list and the mistake explains itself.

A grouped take still needs its order, in any pipeline, however long:

sales |> keep(region != "East") |> take(2, by = product) |> collect()

Error:
! 
illegal: `take ... by` gives the first rows of each group, and nothing has said what order the rows are in, so there is no first. Sort before it: `then sort [when] descending then take 1 by [id]`
  |
3 |   then take 2 by [product]
  |        ^^^^^^^^^^^^^^^^^^

try:
    collect(sales >> keep(col.region != "East")
      >> take(2, by = col.product))
except GodError as refusal:
    print(refusal)


illegal: `take ... by` gives the first rows of each group, and nothing has said what order the rows are in, so there is no first. Sort before it: `then sort [when] descending then take 1 by [id]`
  |
3 |   then take 2 by [product]
  |        ^^^^^^^^^^^^^^^^^^

And a typo is a typo wherever it happens, with the nearest real name offered back:

survey |> keep(regoin == "West") |> collect()

Error:
! 
illegal: there is no column called `regoin`. Did you mean `region`? The table has: respondent, name, region, joined, q1_score, q2_score, q3_score, q4_score, q5_score, q6_score, q7_score, q8_score
  |
2 |   then keep where ([regoin] is "West")
  |                     ^^^^^^

try:
    collect(survey >> keep(col.regoin == "West"))
except GodError as refusal:
    print(refusal)


illegal: there is no column called `regoin`. Did you mean `region`? The table has: respondent, name, region, joined, q1_score, q2_score, q3_score, q4_score, q5_score, q6_score, q7_score, q8_score
  |
2 |   then keep where ([regoin] is "West")
  |                     ^^^^^^

Last, a habit carried in from another tool is refused by name, with the grammar’s own word offered in its place. This one is written in the text form, so the call is the same string in both tabs:

run('survey then pick except [q1_score]')

Error:
! 
illegal: the grammar writes this as `all_but`, so there is one spelling rather than one per language. Write `pick all_but [a, b]` instead of `pick except [a, b]`
  |
1 | survey then pick except [q1_score]
  |                  ^^^^^^

try:
    run('survey then pick except [q1_score]')
except GodError as refusal:
    print(refusal)


illegal: the grammar writes this as `all_but`, so there is one spelling rather than one per language. Write `pick all_but [a, b]` instead of `pick except [a, b]`
  |
1 | survey then pick except [q1_score]
  |                  ^^^^^^

exclude, drop, omit and without are met the same way, each one answering with all_but.

None of these cost you anything. Nothing ran, nothing half-completed, and in each case the message is shorter than the documentation you did not have to open. Refusals are how this grammar teaches at the moment of need, which is why this book keeps putting them on stage.

11.5 The checkpoint

This part made three claims about you, and they are now checkable. Read any pipeline in this part aloud, cold. Given a question about one table, name the verbs that answer it before typing. And predict, before running, whether a sentence will answer or refuse. If any of the three fails, the chapter that repairs it is never far back, and they are all short.

From here the book forks, and both branches are honest. Read on in order, and the next part changes the shape of a table. Or jump to the cookbook near the end of the book, which answers questions in the book’s small vocabulary, most of it already yours, and come back to the middle parts as your questions demand them. A grammar is not a syllabus; from this page on, you are allowed to need things in your own order.