The Case

For the first case, we’re to find the missing (or more correctly, mis-shelved) book.

Solving

The solution is pretty straight-forwards; add up the weights of all the books on a shelf (as reporting by RFID) and find the one that doesn’t match the total weight reported by the shelf:

  • Expand rf_ids
  • Joining to the Books table
  • Sum up the weight of the books by shelf
  • Find the shelf

Plot twist - the shelves report a slightly different weight to the sum total of all books. I guess that’s the dust in the library. Hey, who turned out the lights?

Solution

let book_weight = toscalar(Books
 | where book_title == "De Revolutionibus Magnis Data"  | project weight_gram);
Shelves
| mv-expand rf_ids
| extend rf_id = tostring(rf_ids)
| join Books on rf_id
| summarize  sum(weight_gram) by shelf, total_weight
| where (total_weight - sum_weight_gram) > book_weight