# Example SQL Queries for Hevy Analytics
This document contains example SQL queries you can use (or ask Claude to write) to analyze your workout data.
## Basic Queries
### List all exercises you've done
```sql
SELECT DISTINCT exercise_title
FROM workout_sets
ORDER BY exercise_title;
```
### Count total workouts
```sql
SELECT COUNT(DISTINCT title || start_time) as total_workouts
FROM workout_sets;
```
### Recent workout summary
```sql
SELECT
title,
DATE(start_time) as date,
COUNT(DISTINCT exercise_title) as num_exercises,
COUNT(*) as total_sets
FROM workout_sets
WHERE set_type != 'warmup'
GROUP BY title, start_time
ORDER BY start_time DESC
LIMIT 10;
```
## Progress Tracking
### Find PRs (Personal Records) for an exercise
```sql
-- Heaviest single rep
SELECT
DATE(start_time) as date,
exercise_title,
MAX(weight_lbs) as max_weight,
reps
FROM workout_sets
WHERE exercise_title = 'Bench Press (Barbell)'
AND set_type != 'warmup'
GROUP BY DATE(start_time), exercise_title
ORDER BY max_weight DESC
LIMIT 5;
```
### Best total load (weight × reps) for an exercise
```sql
SELECT
DATE(start_time) as date,
exercise_title,
weight_lbs,
reps,
(weight_lbs * reps) as total_load
FROM workout_sets
WHERE exercise_title = 'Bench Press (Barbell)'
AND set_type != 'warmup'
AND weight_lbs IS NOT NULL
AND reps IS NOT NULL
ORDER BY total_load DESC
LIMIT 10;
```
### Progress over time for an exercise
```sql
SELECT
strftime('%Y-%m', start_time) as month,
exercise_title,
AVG(weight_lbs) as avg_weight,
MAX(weight_lbs) as max_weight,
COUNT(*) as total_sets
FROM workout_sets
WHERE exercise_title = 'Squat (Barbell)'
AND set_type != 'warmup'
AND weight_lbs IS NOT NULL
GROUP BY month, exercise_title
ORDER BY month;
```
## Volume Analysis
### Total volume per exercise (last 3 months)
```sql
SELECT
exercise_title,
SUM(weight_lbs * reps) as total_volume,
COUNT(*) as total_sets,
COUNT(DISTINCT DATE(start_time)) as days_trained
FROM workout_sets
WHERE set_type != 'warmup'
AND weight_lbs IS NOT NULL
AND reps IS NOT NULL
AND start_time >= date('now', '-3 months')
GROUP BY exercise_title
ORDER BY total_volume DESC;
```
### Weekly volume trend
```sql
SELECT
strftime('%Y-W%W', start_time) as week,
SUM(weight_lbs * reps) as total_volume,
COUNT(DISTINCT DATE(start_time)) as workout_days
FROM workout_sets
WHERE set_type != 'warmup'
AND weight_lbs IS NOT NULL
AND reps IS NOT NULL
GROUP BY week
ORDER BY week DESC
LIMIT 12;
```
## Muscle Group Analysis
### Volume by muscle group (requires taxonomy)
```sql
-- Example for chest exercises
SELECT
exercise_title,
SUM(weight_lbs * reps) as total_volume,
COUNT(DISTINCT DATE(start_time)) as times_trained
FROM workout_sets
WHERE exercise_title IN (
'Bench Press (Barbell)',
'Incline Bench Press (Barbell)',
'Dumbbell Press (Dumbbell)',
'Cable Fly (Cable)'
)
AND set_type != 'warmup'
AND weight_lbs IS NOT NULL
AND reps IS NOT NULL
AND start_time >= date('now', '-6 months')
GROUP BY exercise_title
ORDER BY total_volume DESC;
```
## Plateau Detection
### Exercises with no PR in last 90 days
```sql
-- Find exercises where max weight hasn't increased
WITH recent_max AS (
SELECT
exercise_title,
MAX(weight_lbs) as current_max
FROM workout_sets
WHERE start_time >= date('now', '-90 days')
AND set_type != 'warmup'
GROUP BY exercise_title
),
historical_max AS (
SELECT
exercise_title,
MAX(weight_lbs) as historical_max
FROM workout_sets
WHERE start_time < date('now', '-90 days')
AND set_type != 'warmup'
GROUP BY exercise_title
)
SELECT
r.exercise_title,
r.current_max,
h.historical_max,
(r.current_max - h.historical_max) as weight_gain
FROM recent_max r
JOIN historical_max h ON r.exercise_title = h.exercise_title
WHERE r.current_max <= h.historical_max
ORDER BY exercise_title;
```
## Frequency Analysis
### How often do you train each exercise?
```sql
SELECT
exercise_title,
COUNT(DISTINCT DATE(start_time)) as days_performed,
MIN(DATE(start_time)) as first_performed,
MAX(DATE(start_time)) as last_performed,
ROUND(
CAST(COUNT(DISTINCT DATE(start_time)) AS FLOAT) /
(JULIANDAY(MAX(start_time)) - JULIANDAY(MIN(start_time))) * 7,
2
) as times_per_week
FROM workout_sets
WHERE set_type != 'warmup'
GROUP BY exercise_title
HAVING days_performed > 5
ORDER BY times_per_week DESC;
```
## Advanced Analysis
### Best progression in last 6 months (by total load improvement)
```sql
WITH recent_best AS (
SELECT
exercise_title,
MAX(weight_lbs * reps) as best_load
FROM workout_sets
WHERE start_time >= date('now', '-3 months')
AND set_type != 'warmup'
GROUP BY exercise_title
),
older_best AS (
SELECT
exercise_title,
MAX(weight_lbs * reps) as best_load
FROM workout_sets
WHERE start_time >= date('now', '-6 months')
AND start_time < date('now', '-3 months')
AND set_type != 'warmup'
GROUP BY exercise_title
)
SELECT
r.exercise_title,
o.best_load as old_best,
r.best_load as recent_best,
(r.best_load - o.best_load) as improvement,
ROUND(((r.best_load - o.best_load) / CAST(o.best_load AS FLOAT)) * 100, 1) as pct_improvement
FROM recent_best r
JOIN older_best o ON r.exercise_title = o.exercise_title
WHERE r.best_load > o.best_load
ORDER BY pct_improvement DESC;
```
### Identify supersets
```sql
SELECT
superset_id,
GROUP_CONCAT(DISTINCT exercise_title) as exercises,
COUNT(*) as total_sets,
DATE(start_time) as workout_date
FROM workout_sets
WHERE superset_id IS NOT NULL
GROUP BY superset_id, DATE(start_time)
ORDER BY workout_date DESC
LIMIT 20;
```
### RPE distribution for main lifts
```sql
SELECT
exercise_title,
AVG(rpe) as avg_rpe,
MIN(rpe) as min_rpe,
MAX(rpe) as max_rpe,
COUNT(*) as sets_tracked
FROM workout_sets
WHERE exercise_title IN (
'Bench Press (Barbell)',
'Squat (Barbell)',
'Deadlift (Barbell)',
'Overhead Press (Barbell)'
)
AND rpe IS NOT NULL
AND set_type != 'warmup'
GROUP BY exercise_title
ORDER BY avg_rpe DESC;
```
## Time-Based Queries
### Training volume by day of week
```sql
SELECT
CASE CAST(strftime('%w', start_time) AS INTEGER)
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 day_of_week,
COUNT(DISTINCT DATE(start_time)) as num_workouts,
SUM(weight_lbs * reps) as total_volume
FROM workout_sets
WHERE set_type != 'warmup'
AND weight_lbs IS NOT NULL
AND reps IS NOT NULL
GROUP BY strftime('%w', start_time)
ORDER BY CAST(strftime('%w', start_time) AS INTEGER);
```
### Longest streak of consecutive training days
```sql
-- This is complex in SQLite, but here's a simplified version
SELECT
COUNT(DISTINCT DATE(start_time)) as training_days,
MIN(DATE(start_time)) as period_start,
MAX(DATE(start_time)) as period_end
FROM workout_sets
WHERE start_time >= date('now', '-30 days');
```
## Tips for Writing Queries
1. **Always filter out warmup sets** unless you specifically want them:
```sql
WHERE set_type != 'warmup'
```
2. **Handle NULL values** when calculating volume:
```sql
WHERE weight_lbs IS NOT NULL AND reps IS NOT NULL
```
3. **Group by workout** using both title and start_time:
```sql
GROUP BY title, start_time
```
4. **Format dates** for readability:
```sql
DATE(start_time) as date
strftime('%Y-%m', start_time) as month
```
5. **Consider form resets** by checking `get_tracking_conventions()` before analyzing progress
6. **Use exercise taxonomy** to group exercises by muscle group (requires joining with taxonomy data or manual IN clauses)
