Walk Through Guide for Kusto Detective Agency 2022, Case #2 Solution
Are you excited too? Because things get tricky📈
The second case riddle is:
The key takeaways from the riddle:
- Election fraud has happened, but how — is the question
- Kastor and Gaul were supposed to be in a close battle
- Poppy won the elections!🐟
The ambiguity is thrilling, what happened in the Digitown elections?
There are many ways to fraud an election, and the journey to find it would require us to be creative and think outside of the voting box!
⚠SPOILER ALERT — THE SOLUTION DESCRIBED BELOW⚠
So let’s delve into the data, by leveraging the | take 1, to see a single row.
The Votes table has; Timestamp, Vote, IP Address, Voter Hash ID.
Optimistically, we can see if the same voter has voted multiple times?
Let’s count by Voter Hash ID, and filter where voter count more than once:
Altogether, success can’t be that easy! 😤
Only a few 9 voters out of 5030412 have participated twice:
Let’s see if the time pattern reveals anything interesting?
We shall utilize bin operator to group votes counts into bins of 15 minutes.
Then we render a beautiful chart to see the vote counts for each candidate in each bin timestamp:
Still, nothing interesting pops out…
How about combining those two? 🔀
Counting votes for each IP in a specific timestamp bin 📊
That tells us how many votes each candidate gets each minute:
Usually, candidates get each minute between 1–10 votes.
Altogether Poppy had minutes where he got more than 11 votes per minute.
This is a great lead to remove the fraudulent votes!
Let’s remove votes of IPs where there have been more than 11 in a single minute!
The iff function allows setting a value of 0 when the condition for fraudulent votes is detected otherwise the original values for votes from that IP:
Enjoyed this article? Feel free to long-press the 👏 button below 😀
Click for Next Case Solution ➡
