Microsoft has released a Kusto Query Language(KQL) challenge for us to solve. It consists of five different challenges.
We completed case 1, lets try the second challenge – “Election fraud?“
The city Digitown has conducted an election for the city’s new mascot. In total there were over 5 million votes and only four candidates made it to the final round. The polls indicated a close battle between two candidates; Kastor and Gaul. But all of a sudden the ex-mayor’s fish got 51.7% of the votes. Election fraud… We need to figure out who as actually won the election.
The description gives us a query to run. This query calculates the percentages of each candidate. Lets run it!
Votes | summarize Count=count() by vote | as hint.materialized=true T | extend Total = toscalar(T | summarize sum(Count)) | project vote, Percentage = round(Count*100.0 / Total, 1), Count | order by Count
A quick look at the votes indicates that Poppy has gotten lots of votes within sporadic short timeframes. So how do we filter out these fraudulent votes?
My first instinct is to count one vote for Poppy per actual minute the votes are submitted by IP. I’ll show you the actual query I made and tell you a bit about it.
let TempVotes = Votes
| where vote == "Poppy"
| distinct datetime_part("hour",Timestamp), datetime_part("minute",Timestamp), via_ip
| union (Votes | where vote !="Poppy");
Line 1 – Creates a variable based on the table Votes
Line 2 – All the votes for Poppy
Line 3 – One vote per distinct combination of hour, minute and via_ip
Line 4 – Consolidates the rest of the votes back into our table
Based on this variable we can perform queries. So now we have the actual votes without fraud, we just need to calculate it.
TempVotes | summarize Count=count() by vote | as hint.materialized=true T | extend Total = toscalar(T | summarize sum(Count)) | project vote, Percentage = round(Count*100.0 / Total, 1), Count | order by Count
Line 1 – Our aggregated table based on actual votes
Line 2 – Aggregates a table where Count is the count of votes
Line 3 – Binds the name T to the output of line 2
Line 4 – Extends Total as the sum of count in T
Line 5 – Projects vote, calculated column Percentage and Count
Line 6 – Order the output by Count
This gives us the actual votes and percentages. So we’ve figured out that Kastor is actually the winner of the election!