Walk Through Guide for Kusto Detective Agency 2022, Case #1 Solution
Are you ready for trouble? Because here the challenge begins 🙂
The first case riddle is:
The key takeaways from the riddle:
- Each book weight is known
- Each book, but the missing one, has a RFID badge
- Each shelf's actual total book weight is known and the RFIDs of the books on it
We know the details of the missing book:
Title: De Revolutionibus Magnis Data
Written By: Gustav Kustov
Published: 1613
⚠SPOILER ALERT — THE SOLUTION DESCRIBED BELOW⚠
The missing book is located on a shelf, so the actual weight of the self is higher than the sum of weights that are reported on that shelf because the book is missing the RFID.
So let’s delve into the data, by leveraging the | take 1, to see a single row.
The Books table has; RFID, Book Title, Publish Data, Author, Language, Number of Pages, and Weight Gram.
The Shelves table has; id, RFIDs of the books on it, and the total weight.
For each shelf, we’d like to calculate the expected total weight of the books on the shelf, so we can compare it with the reported total weight.
Altogether, we are given the RFIDs on the shelf as an array: if only we had them separately, we could have used the RFID of each book to find its weight from the books table, and then sum by shelf.
Lucky us🍀 KQL has an mv-expand operator that lets us to split the RFIDs array of the shelf:
This results in a row for each book RFID and each shelf:
Now we’d like to sum of weights of the books on the self, so let’s join the Shelves table with the Books table, then we can utilize the summarize operator to do this aggregative calculation of sumof weights of books by shelf.
We’re also interested in the reported weight of the shelf, we can take_any of the weights of the duplicated (for each book) shelf rows.
Lastly, we’d like to find the difference in weights of the shelves between :
- The actual measured weight on the shelf
- The reported (according to books with RFIDs on the shelf)
Keeping in mind the missing book has no RFID tag, so the actual weight is more than reported by the missing book weight:
The shelf on which the missing book is located is 4242!
4242 in which the missing book is locatedAfter solving this case we feel confident to be closer to finding the answer to life, the universe, and everything! 🌟
Enjoyed this article? Feel free to long-press the 👏 button below 😀
Click Here for Next Case Solution ➡
