Your Year in Data: How SQL Helps You Summarize 12 Months of Information

Ever wanted to analyze your own year like Spotify Wrapped does? With a few SQL queries, you can turn your workout data — or any dataset — into a personal year-in-review.

How was your year? How much did you spend? What kind of music did you listen to the most? How many workouts did you complete — or how many movies did you watch?

All of that information already exists in your digital life. Your banking app tracks expenses, Spotify keeps your listening history, and your fitness tracker logs every run. The only thing missing is your own analysis — one that tells the story you care about.

That’s where SQL comes in. With just a few simple queries, you can summarize an entire year of personal data — whether it’s your budget, playlists, or workouts — and uncover insights most apps never show you. Even if you’re not a SQL pro, it’s not too late to start. It’s October — the perfect time to learn SQL and get ready to create your own “year in data” before this one ends.

Not sure where to begin? Start with our SQL Basics course — over 100 hands-on exercises designed to help you build real SQL skills from day one. Every lesson is interactive, so you’ll gain confidence fast and start writing your own queries in no time.

In this article, we’ll use a fictional workout dataset to show how SQL helps you find your total distance, your best month, your most active day, and even your personal records. Once you understand the basics, you can apply the same ideas to any kind of data you track.

Meet Your Dataset: A Simple Workout Tracker

To make things practical, let’s work with a small, Strava-like dataset — something you could easily create yourself. Imagine a table called workouts, where each row represents one activity from your year: a run, a bike ride, or a swim.

Here’s what it looks like:

Most workouts happened close to home in London, with the occasional longer ride outside the city. Each column tells part of your story: when you trained, what kind of activity it was, how far you went, how long it took, and even where it happened. It’s the kind of information your watch or app collects automatically — you just need a way to read it.

That’s where SQL shines. By running a few simple queries, you can turn this raw data into a summary of your entire year: how far you went, which month you were most active, and what your personal bests looked like.

Let’s start by looking at your yearly totals.

Step 1 – Your Yearly Summary

Let’s start with the big picture. Before diving into details, it’s helpful to know how much ground you covered this year — total distance, total training time, and how many workouts you completed.

With SQL, you can get all of that in a single query:

SELECT 
  SUM(distance_km) AS total_distance, 
  SUM(duration_min) AS total_duration, 
  COUNT(*) AS total_sessions 
FROM workouts 
WHERE EXTRACT(YEAR FROM date)=2025;

This query gives you a quick summary of your year. You might discover something like this:

• Total distance: 405 km
• Total time: 1,200 minutes (that’s 20 hours!)
• Total sessions: 42 workouts

It’s a satisfying snapshot of your effort — your entire year, condensed into three numbers.

If you’d like to learn how to write queries like this, check out our SQL Basics course. It includes a full section on creating simple statistics, helping you build confidence with real, hands-on SQL practice.

But this overview has one small limitation: it combines everything together. If your table includes runs, rides, or swims, those activities have very different distances and speeds. Adding them up doesn’t give the full story.

That’s why the next step is to break your statistics down by activity type.

Step 2 – Statistics by Activity Type

A 40 km ride and a 10 km run don’t belong in the same bucket — cycling distances are naturally higher, while running takes more time per kilometer. To really understand your effort, you need to separate your data by activity type.

Here’s how to do that:

SELECT 
  activity_type, 
  SUM(distance_km) AS total_distance, 
  ROUND(AVG(distance_km),1) AS avg_distance, 
  ROUND(AVG(avg_speed_kmh),1) AS avg_speed, 
  COUNT(*) AS total_sessions 
FROM workouts 
GROUP BY activity_type 
ORDER BY total_distance DESC;

This query groups your data by each activity and shows:

• how far you went in total,
• how long your average session was,
• your average speed, and
• how many times you trained.

You might see something like this:

Now your data tells a more complete story. Maybe you cycled farther overall, but you ran more often. Or perhaps your average running pace improved compared to your rides.

This is where SQL becomes a real analysis tool — helping you see the differences, not just the totals.

If you want to understand how queries like this work — and practice building your own — start with our SQL Basics course. It’s packed with interactive exercises that guide you through the essentials step by step, so you can quickly learn to group, filter, and summarize your data with confidence.

Next, let’s zoom in further and explore how your performance changed month by month.

Step 3 – Monthly Statistics per Activity

Once you’ve seen your overall and per-activity totals, the next step is to understand how your training evolved month by month. SQL makes this easy for example with the DATE_TRUNC() function, which lets you group data by a specific time period — for example, by month.

Here’s the query

SELECT
    DATE_TRUNC('month', date) AS month,
    activity_type,
    COUNT(*) AS sessions,
    SUM(distance_km) AS total_distance,
    ROUND(AVG(distance_km), 1) AS avg_distance,
    ROUND(AVG(avg_speed_kmh), 1) AS avg_speed,
    MAX(distance_km) AS longest_distance
FROM workouts
GROUP BY month, activity_type
ORDER BY month, activity_type;

When you write DATE_TRUNC('month', date), SQL “rounds” every date down to the first day of its month. So, all workouts from January will be grouped under 2025-01-01, February under 2025-02-01, and so on. This helps you summarize data for each month in your dataset.

Note: The  DATE_TRUNC() function works in PostgreSQL, Snowflake, Redshift, and BigQuery. Other databases use different syntax:

• MySQL: DATE_FORMAT(date, '%Y-%m-01')
• SQL Server: DATEFROMPARTS(YEAR(date), MONTH(date), 1)
• SQLite: strftime('%Y-%m-01', date)

If you want to learn more about working with date and time functions, explore our Standard SQL Functions course — or one of its counterparts for MySQL, PostgreSQL, or SQL Server. Each version teaches you how to use functions like these in the database you work with every day.

The query shows, for each activity and month:

• how many sessions you completed,
• your total distance,
• your average distance,
• your average speed, and
• your longest workout that month.

Example results:

With this query, you can see how your performance changed throughout the year — when you trained most often, when your distances peaked, and how your average pace evolved.

Next, let’s look at your personal bests — the individual workouts that stood out from the rest.

Step 4 – Highlights and Personal Bests

Now that you’ve seen your monthly progress, let’s move on to the most rewarding part — your personal records. Every athlete loves to know their longest, fastest, or toughest workouts, and SQL can find those in seconds.

Let’s start with your longest ride and longest run.

SELECT *
FROM workouts
WHERE distance_km = (
    SELECT MAX(distance_km)
    FROM workouts
    WHERE activity_type = 'Ride'
);

You can repeat the same query for runs — just change 'Ride' to 'Run'. This subquery finds the maximum distance for each activity type and returns the full workout details, so you can see when and where that record happened.

If you’d like to learn more about how subqueries work — and how to use them to compare, filter, or calculate values — check out our SQL Subqueries course. It’s a hands-on way to practice writing queries inside other queries, using real-world datasets similar to this one.

Similarly, you can find your fastest workout (based on average speed):

SELECT *
FROM workouts
WHERE avg_speed_kmh = (
    SELECT MAX(avg_speed_kmh)
    FROM workouts
    WHERE activity_type = 'Run'
);

You can use this structure for other metrics too — highest elevation, longest duration, most calories burned, or even your biggest month.

These personal bests are a fun way to end the analysis. They give your year a set of highlights — the workouts you’ll remember most when you look back.

Next, we’ll finish with one last perspective: when you train the most — your day-of-the-week habits.

Step 5 – Day-of-the-Week Statistics

Everyone has training habits — maybe you always ride on weekends or squeeze in short runs during the week. SQL can uncover these patterns with one simple query.

SELECT
    EXTRACT(DOW FROM date) AS weekday_number,
    CASE EXTRACT(DOW FROM date)
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS weekday,
    activity_type,
    COUNT(*) AS sessions,
    ROUND(AVG(distance_km), 1) AS avg_distance
FROM workouts
GROUP BY weekday_number, weekday, activity_type
ORDER BY (CASE WHEN weekday_number = 0 THEN 7 ELSE weekday_number END), activity_type;

The EXTRACT(DOW FROM date) function returns the day of the week as a number (0 for Sunday through 6 for Saturday). The CASE statement converts those numbers into readable names, and the final ORDER BY ensures the days appear from Monday to Sunday.

If you want to practice using CASE expressions and date functions together — just like in this query — check out our Creating Basic SQL Reports course. It shows how to combine these tools to group, label, and summarize data effectively, helping you build clean, insightful reports step by step.

Example results:

From this table, you can instantly see your rhythm — weekday runs, weekend rides, or maybe a recovery pattern between.

This final query completes your “year in data.” You now know your yearly totals, how each activity type performed, when you peaked, your personal bests, and how your weekly routine shaped the whole year.

Your Year, Your Data

If you enjoyed exploring this simple workout dataset, imagine what you could do with your own data — from spending habits to streaming history. It’s October — the perfect moment to start learning SQL and build your own yearly summary before the year ends.

You’ve just seen how a few simple SQL queries can turn raw data into real insights. With SUM, AVG, GROUP BY, and a few helper functions, you built a complete summary of your year — totals, monthly trends, personal records, and even your weekly routine.

And this was only one dataset — your workouts. The same approach works for almost anything you track:

• your personal budget (total expenses, monthly averages, biggest spending categories),
• your calendar (how many meetings you had, when you’re busiest),
• or your Spotify stats (most-played artists, songs per month, total listening time).

Once you understand how to ask questions with SQL, you can analyze any part of your life or work that leaves a data trail.

If you want to learn how to do this properly — step by step, with hands-on practice — start with LearnSQL.com. The All Forever SQL Plan gives you lifetime access to all our courses, so you can go from beginner to confident analyst at your own pace. It’s October — the perfect time to start learning and prepare your own “year in data.”

With the All Forever SQL Plan, you unlock every current and future course for a single price — no subscriptions, no renewals. You get beginner-to-advanced paths (basics, queries, subqueries, joins, window functions, reporting), practice tracks with real datasets, interactive exercises, and certificates. Learn at your pace, revisit lessons anytime, and keep building skills across topics like analytics, reporting, and data prep — all under one plan.