• Onboarding: Here
• Challenge 1: This article
• Challenge 2: Here
• Challenge 3: Here
• Challenge 4: Here
• Challenge 5: Here
• Challenge 6: Here
• Challenge 7: Here
• Challenge 8: Here
• Challenge 9: Coming soon
• Challenge 10: Coming soon

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.

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.

//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)