Challenges
In this first case we’re asked to solve a billing problem, not the most exciting thing but certainly interesting with some real-world applications for the use of data. I quite enjoyed this challenge as it reminded me to keep things simple and not discount any ideas as silly just yet.
General advice
For this case the wording tripped me up a little bit, make sure you understand what’s being asked and check out the training if necessary. I will say, while I like the idea of the training it eventually put me on the wrong track, so use it but also keep an open mind.
Challenge: Case 1
Dear Detective,
Welcome to the Kusto Detective Agency! We’re thrilled to have you on board for an exciting new challenge that awaits us. Get ready to put your detective skills to the test as we dive into a perplexing mystery that has struck Digitown.
Imagine this: It’s a fresh new year, and citizens of Digitown are in an uproar. Their water and electricity bills have inexplicably doubled, despite no changes in their consumption. To make matters worse, the upcoming mayoral election amplifies the urgency to resolve this issue promptly.
But fear not, for our esteemed detective agency is on the case, and your expertise is vital to crack this mystery wide open. We need your keen eye and meticulous approach to inspect the telemetry data responsible for billing, unravel any hidden errors, and set things right.
Last year, we successfully served Mayor Gaia Budskott, leaving a lasting impression. Impressed by our work, the city has once again turned to us for assistance, and we cannot afford to disappoint our client.
The city’s billing system utilizes SQL (an interesting choice, to say the least), but fret not, for we have the exported April billing data at your disposal. Additionally, we’ve secured the SQL query used to calculate the overall tax. Your mission is to work your magic with this data and query, bringing us closer to the truth behind this puzzling situation.
Detective, we have complete faith in your abilities, and we are confident that you will rise to the occasion. Your commitment and sharp instincts will be instrumental in solving this enigma.
Sincerely,
Captain Samuel Impson.
Right let’s get down to business and get the citizens of Digitown their correct bills!
There are two things wrong with the billing run this month and you’ll have to find both to get the right answer. KQL commands that will be useful are arg_min and distinct.
There is a bit of an investigation that needs to be done to uncover the issues with the data and there are various angles you can take, such as looking at specific houses or dates just to name a couple.
Solution – Spoilers below
Have you found the two things wrong with the billing?
//The most obvious issue is that some customers are being double billed, so we need to remove those duplicates. Also, it turns out that some customers are using negative water and electricity, what doesn’t seem possible so let’s get rid of that too.
Consumption
| where Consumed > 0
| distinct Timestamp,HouseholdId,MeterType, Consumed
| summarize TotalConsumed = sum(Consumed) by MeterType
| lookup Costs on MeterType
| extend TotalCost = TotalConsumed*Cost
| summarize sum(TotalCost)
Great work detectives! This case gave me a nice opportunity to stretch my “KQL legs” and i found it to be a fun experience. I’m definitely looking forward to the next one.
