and Find the Best Time to Travel

Looking for the perfect time to take a vacation? With just a few SQL queries and real weather data from Szeged, Hungary, you can figure out which months are hot, rainy, or great for a walk in the sun. This mini-tutorial is a fun, practical way to sharpen your SQL skills—and maybe plan your next trip while you're at it.

Looking for sunshine and low rain?

Planning a vacation isn't just about picking a destination. It’s also about timing—when is the weather actually pleasant?

With just a few SQL queries, you can answer questions like: What’s the hottest month in a city? When does it rain most often? Are windy days common in spring? In this tutorial, you’ll work with real historical weather data from Szeged, Hungary, and practice essential SQL skills like SELECT, GROUP BY, HAVING, and ORDER BY. It’s a hands-on mini project that combines real-world practice with a bit of travel curiosity.

Want to brush up on the basics first? Try the SQL Basics course at LearnSQL.com—it’s beginner-friendly, interactive, and teaches everything you need to follow this tutorial.

You can also adapt these queries to analyze weather in other locations. Historical Weather API

Looking for data from other locations? The Open-Meteo Historical Weather API is a great free resource for downloading weather history by city or coordinate.

SQL isn’t just for reports and dashboards—it can even help you plan your next holiday. Let’s dive in.

Getting Started: Load the Weather Dataset

We’ll use a public Weather in Szeged 2006-2016 dataset from Kaggle, which contains daily weather data from 2006 to 2016.

1. Download the data: Visit the Kaggle page and download the file weatherHistory.csv
   2. Prepare the file for import

To import this into a database of your choice, you need to:

• Remove the +01:00 from the Formatted Date You can use your editor’s Search and Replace function to do it.
• Rename the headers to use underscores instead of spaces

You can edit the file using Excel, Notepad, or any text editor.

Here’s the cleaned header row:

formatted_date,summary,precip_type,temperature_c,apparent_temperature_c,humidity,wind_speed_kmh,wind_bearing_deg,visibility_km,pressure_mb,daily_summary

3. Create the table in your database

CREATE TABLE szeged_weather (
  formatted_date TEXT,
  summary TEXT,
  precip_type TEXT,
  temperature_c FLOAT,
  apparent_temperature_c FLOAT,
  humidity FLOAT,
  wind_speed_kmh FLOAT,
  wind_bearing_deg FLOAT,
  visibility_km FLOAT,
  pressure_mb FLOAT,
  daily_summary TEXT
);

4. Import the CSV File

In PostgreSQL:

COPY szeged_weather FROM '/path/to/weatherHistory.csv' DELIMITER ',' CSV HEADER;

or in MySQL:

LOAD DATA INFILE '/path/to/weatherHistory.csv' INTO TABLE szeged_weather
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Make sure your database is configured to allow file imports.

Step-by-Step: Analyzing the Weather Data in SQL

Start by Exploring the Data

Before you dive into analysis, it helps to understand what kind of data you're working with. Let’s preview a few rows from the dataset.

SQL Query

SELECT *
FROM szeged_weather
LIMIT 5;

Sample Output

How This Works

• SELECT * returns all columns — useful for a first glance.
• LIMIT 5 returns only the first 5 rows. It keeps the output manageable and does not overwhelm the database.

What You Just Practiced

• Previewing data structure
• Scanning available columns
• Confirming data format and types

🔗 New to SQL? The SQL Basics course on LearnSQL.com walks you through these first steps interactively — perfect if you're just getting started.

What This Means for You

You’ll see columns like formatted_date, temperature_c, wind_speed_kmh, and summary. This gives you context for what’s possible — and which fields you can filter or group later.

What’s the Average Temperature in Szeged?

Let’s start with something simple: the average temperature across the entire dataset.

SQL Query

SELECT 
  ROUND(AVG(temperature_c), 1) AS avg_temp
FROM szeged_weather;

Sample Output

How This Works

• AVG() calculates the average value.
• ROUND(..., 1) makes it easier to read by showing just one decimal.

What You Just Practiced

• Basic aggregation with AVG()
• Formatting numerical output
• Getting a big-picture number from raw data

🔗 Want to master SQL functions? Check out the Standard SQL Functions course on LearnSQL.com — it covers AVG(), SUM(), MIN(), MAX(), and more, with practical exercises and real use cases

What This Means for You

The result tells you the average daily temperature in Szeged over the entire 10-year period. This is useful as a reference point — later, you can compare specific months or days to this baseline.

Which Month Is Warmest in Szeged?

You’re looking for warm, sunny days—maybe to visit local markets, take a walk by the river, or just avoid packing a coat. Let’s find out which month has the highest average temperature in Szeged.

SQL Query

SELECT 
  EXTRACT(MONTH FROM formatted_date) AS month,
  ROUND(AVG(temperature_c), 1) AS avg_temp
FROM szeged_weather
GROUP BY month
ORDER BY month;

Sample Output

How This Works

• EXTRACT(MONTH FROM ...) pulls the month from each date.
• AVG(temperature_c) calculates the average monthly temperature.
• ROUND(..., 1) formats it to one decimal.
• GROUP BY and ORDER BY structure the results for easy comparison.

What You Just Practiced

• Aggregation with AVG()
• Working with date parts using EXTRACT()
• Grouping and sorting query results

📘 Want more structured practice?

Try the SQL Group BY Practice course on LearnSQL.com. This beginner-level course focuses on using GROUP BY with aggregate functions like AVG(), SUM(), MIN(), and COUNT() across different scenarios and datasets. It’s perfect for reinforcing what you just practiced in an interactive format.

What This Means for You

• July is the warmest month in Szeged, with August close behind.
• May and September are mild and good for those who prefer comfortable weather.
• Avoid January through March if you’re hoping for warm days.

What Are the Most Common Weather Types?

Let’s see what kind of weather you’re most likely to experience, based on the summary column.

SQL Query

SELECT 
  summary,
  COUNT(*) AS occurrences
FROM szeged_weather
GROUP BY summary
ORDER BY occurrences DESC
LIMIT 10;

Sample Output

How This Works

• COUNT(*) tallies how often each summary appears.
• GROUP BY summary clusters identical values.
• ORDER BY ... DESC and LIMIT help focus on the top 10 weather types.

What You Just Practiced

• Frequency analysis with COUNT(*)
• Sorting descending results
• Limiting output with LIMIT

What This Means for You

• "Partly cloudy" is very common—so you can expect mixed skies.

If your perfect vacation requires consistent sun, check how often "Clear" appears.

How Often Does It Rain Each Month?

Warm weather is great—but not if it’s constantly raining. Let’s figure out how often it rains in Szeged each month — not just total rainfall, but how many hours typically include rain.

Each row in this dataset represents one hour of weather. So when we calculate rainy percentages, we’re answering:  “Out of all hours in a given month, how many had rain?”

SQL Query

SELECT 
  EXTRACT(MONTH FROM formatted_date) AS month,
  ROUND(
    SUM(CASE WHEN LOWER(summary) LIKE '%rain%' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
    1
  ) AS percent_rainy
FROM szeged_weather
GROUP BY month
ORDER BY month;

Sample Output

How This Works

• EXTRACT(MONTH FROM formatted_date) gives us the month number (1–12)
• LOWER(summary) makes sure we match words like "Rain" or "rain"
• CASE WHEN ... THEN 1 ELSE 0 END checks each row:
  • If it contains "rain" → return 1
  • If not → return 0
• SUM(...) adds up all the 1s — that’s the number of rainy hours in that month
• COUNT(*) gives us the total hours in the month
• We divide, multiply by 100, and round the result to get a clean percentage

What You Just Practiced

• Using EXTRACT() to group data by month
• Applying a conditional counter with CASE WHEN inside SUM()
• Making text searches case-insensitive with LOWER()
• Calculating percentages from grouped data
• Formatting numbers with ROUND()

📘 Want to sharpen these skills further?

Check out the Creating Basic SQL Reports course on LearnSQL.com. This interactive course covers how to use aggregate functions (AVG(), COUNT(), SUM()), classify data with CASE WHEN, and build simple reports using GROUP BY. It’s beginner-friendly with real-world scenarios and over 90 hands-on exercises to reinforce what you’ve learned.

What This Means for You

If you're planning a vacation, this gives you the real picture:

• June has a higher chance of rain — about 25% of all hours
• August is safer with fewer rainy hours

Use this approach with other conditions, too — snow, fog, or sunny weather. All it takes is changing one word in the LIKE clause.

 Which Days Were Cold and Windy?

Some weather conditions don’t show up in monthly averages. Let's find days that were both cold and windy—conditions that make being outside uncomfortable.

SQL Query

SELECT 
  formatted_date,
  temperature_c,
  wind_speed_kmh
FROM szeged_weather
WHERE temperature_c < 5 AND wind_speed_kmh > 30
ORDER BY temperature_c;

Sample Output

How This Works

• WHERE filters rows that meet both conditions: cold and windy.
• ORDER BY temperature_c lists them from coldest upward.

What You Just Practiced

• Multiple conditions in a filter
• Logical operators (AND)
• Sorting by specific columns

What This Means for You

• These days are rare but harsh. Most fall between November and March.
• This supports the idea that Szeged is not ideal for winter travel.

Challenge: What Was the Hottest Temperature Ever Recorded?

Here’s a chance to apply what you’ve learned. Can you find the single hottest hour in the entire Szeged dataset?

SQL Task

You’ll need:

• MAX(temperature_c) to find the highest recorded value
• ORDER BY temperature_c DESC to sort the data
• LIMIT 1 to return just the hottest hour
• Or use a subquery to return both the temperature and the date it happened

We’ll leave the query to you. Try it, test it, and see what kind of summer extremes Szeged has faced!

Try It with Other Cities

The best part? These queries work anywhere. Whether you're curious about the windiest month in Warsaw or the sunniest season in Sydney, just load a historical weather dataset into SQL and start exploring.

You’re not just practicing—you’re discovering. Real data. Real questions. Real answers.
And maybe even your next vacation spot.

Want to sharpen your SQL skills further? LearnSQL.com offers hands-on, interactive courses where you write and run SQL in your browser. Start with the SQL Basics course and keep building from there.