Using Vanna.AI’s weather dataset effectively

We just introduced a brand new demo dataset on Vanna.AI, Accuweather’s sample data from the Snowflake Data Marketplace.

What’s in the weather data (tables & columns)

We’ll just focus on one table — TOP_CITY_DAILY_IMPERIAL — which has a few cities and a bunch of forecasts around rainfall, temperature, etc.

Here’s a preview —

Asking some questions

So let’s start asking questions. First, a basic one (Q&A here) —

which 10 cities will have the highest temperatures? show city, country, max temp, min temp, and precipitation.

Here’s the SQL —

SELECT 
  city_name,
  country_code,
  TEMPERATURE_MAX,
  TEMPERATURE_MIN,
  MINUTES_OF_PRECIPITATION_TOTAL
FROM forecast.top_city_daily_imperial
ORDER BY TEMPERATURE_MAX DESC
LIMIT 10;

And the results —

Let’s do something a tad bit more complex, and group cities by their length (useless, I know …) (Q&A here)

what’s the average max temperature for cities grouped by the length of their name? include the count of cities, and order by the length of the name.

The SQL generated —

SELECT 
  LENGTH(city_name) as name_length, 
  COUNT(city_name) as city_count, 
  AVG(TEMPERATURE_MAX) as avg_max_temp
FROM forecast.top_city_daily_imperial
GROUP BY name_length
ORDER BY name_length;

And the results —