Solution

  • 3 minutes

You've been asked to write a query that answers the following question:

In the month of April 2007, what were the top five damage-causing storm events in the state of Virginia?

This solution provides one possible way to construct a query that answers the question.

Solution process

  1. Filter out as much information as possible:
    • The time window should be between April 1 and April 30, 2007. Use the where operator to create this filter.
    • You want to return results from only the state of Virginia. Use the where operator to create this filter.
  2. Create a column that sums damage from DamageProperty and DamageCrops by using the project operator.
  3. To figure out which kind of storm caused this damage, you need to return the EventType column. Use the project operator.
  4. You want to see the top five most damaging events. You can use the sort operator, whose default mode is descending. Alternatively, and more efficiently, you can use the top operator.

Solution query

The following query includes all the preceding steps:

Run the query

StormEvents
| where StartTime between (datetime(4-1-2007)..datetime(4-30-2007))
| where State == "VIRGINIA"
| project Damage=DamageProperty+DamageCrops, EventType
| top 5 by Damage

Your results should look something like the following image:

Screenshot of a table with five damage-causing storms in Virginia.

Insights

From this solution's final results, you can see that all the top five damage-causing events in the month of April and in the state of Virginia were all frost or freeze events.