example_queries.sql

-- Example SQL queries for Peloton workout database
-- Generated by import_peloton.py

-- ============================================================
-- BASIC QUERIES
-- ============================================================

-- All workouts with class details
SELECT 
    w.workout_timestamp,
    ct.title,
    i.name as instructor,
    fd.name as discipline,
    wt.name as type,
    w.length_minutes,
    w.calories_burned,
    w.distance_km,
    CASE 
        WHEN w.is_live = 1 THEN 'Live'
        WHEN w.is_live = 0 THEN 'On Demand'
        ELSE 'Just Ride'
    END as workout_mode
FROM workouts w
LEFT JOIN classes c ON w.class_id = c.class_id
LEFT JOIN class_titles ct ON c.title_id = ct.title_id
LEFT JOIN instructors i ON c.instructor_id = i.instructor_id
LEFT JOIN fitness_disciplines fd ON w.discipline_id = fd.discipline_id
LEFT JOIN workout_types wt ON c.type_id = wt.type_id
ORDER BY w.workout_timestamp DESC
LIMIT 20;

-- ============================================================
-- INSTRUCTOR STATISTICS
-- ============================================================

-- Most frequent instructors
SELECT 
    i.name,
    COUNT(*) as workout_count,
    SUM(w.calories_burned) as total_calories,
    ROUND(AVG(w.calories_burned), 1) as avg_calories,
    SUM(w.distance_km) as total_distance_km
FROM workouts w
JOIN classes c ON w.class_id = c.class_id
JOIN instructors i ON c.instructor_id = i.instructor_id
GROUP BY i.name
ORDER BY workout_count DESC;

-- ============================================================
-- TEMPORAL STATISTICS
-- ============================================================

-- Workouts by year and month
SELECT 
    strftime('%Y', workout_timestamp) as year,
    strftime('%m', workout_timestamp) as month,
    COUNT(*) as workouts,
    SUM(calories_burned) as total_calories,
    ROUND(SUM(distance_km), 2) as total_distance_km,
    ROUND(AVG(calories_burned), 1) as avg_calories_per_workout
FROM workouts
GROUP BY year, month
ORDER BY year, month;

-- Workouts by day of week
SELECT 
    CASE CAST(strftime('%w', workout_timestamp) 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(*) as workouts,
    ROUND(AVG(calories_burned), 1) as avg_calories
FROM workouts
GROUP BY strftime('%w', workout_timestamp)
ORDER BY CAST(strftime('%w', workout_timestamp) AS INTEGER);

-- Workouts by hour of day
SELECT 
    strftime('%H', workout_timestamp) as hour,
    COUNT(*) as workouts
FROM workouts
GROUP BY hour
ORDER BY hour;

-- Weekly workout streaks
SELECT 
    strftime('%Y-W%W', workout_timestamp) as week,
    COUNT(*) as workouts,
    SUM(calories_burned) as total_calories
FROM workouts
GROUP BY week
ORDER BY week DESC
LIMIT 20;

-- ============================================================
-- DISCIPLINE BREAKDOWN
-- ============================================================

-- Workouts by fitness discipline
SELECT 
    fd.name as discipline,
    COUNT(*) as workout_count,
    SUM(w.calories_burned) as total_calories,
    ROUND(AVG(w.length_minutes), 1) as avg_length_minutes
FROM workouts w
JOIN fitness_disciplines fd ON w.discipline_id = fd.discipline_id
GROUP BY fd.name
ORDER BY workout_count DESC;

-- Cycling workouts by type
SELECT 
    wt.name as type,
    COUNT(*) as workout_count,
    ROUND(AVG(w.calories_burned), 1) as avg_calories,
    ROUND(AVG(w.avg_watts), 1) as avg_watts
FROM workouts w
JOIN classes c ON w.class_id = c.class_id
JOIN workout_types wt ON c.type_id = wt.type_id
JOIN fitness_disciplines fd ON w.discipline_id = fd.discipline_id
WHERE fd.name = 'Cycling'
GROUP BY wt.name
ORDER BY workout_count DESC;

-- ============================================================
-- CLASS REPETITION
-- ============================================================

-- Classes taken multiple times
SELECT 
    ct.title,
    i.name as instructor,
    c.class_timestamp,
    COUNT(*) as times_taken,
    GROUP_CONCAT(strftime('%Y-%m-%d', w.workout_timestamp), ', ') as workout_dates
FROM workouts w
JOIN classes c ON w.class_id = c.class_id
JOIN class_titles ct ON c.title_id = ct.title_id
JOIN instructors i ON c.instructor_id = i.instructor_id
GROUP BY c.class_id
HAVING times_taken > 1
ORDER BY times_taken DESC, ct.title;

-- Most popular class titles (across all instructors/dates)
SELECT 
    ct.title,
    COUNT(DISTINCT c.class_id) as unique_classes,
    COUNT(*) as total_workouts
FROM workouts w
JOIN classes c ON w.class_id = c.class_id
JOIN class_titles ct ON c.title_id = ct.title_id
GROUP BY ct.title
ORDER BY total_workouts DESC
LIMIT 20;

-- ============================================================
-- PERFORMANCE METRICS
-- ============================================================

-- Personal records for cycling
SELECT 
    'Max Calories (20 min)' as metric,
    MAX(calories_burned) as value,
    workout_timestamp,
    ct.title,
    i.name as instructor
FROM workouts w
LEFT JOIN classes c ON w.class_id = c.class_id
LEFT JOIN class_titles ct ON c.title_id = ct.title_id
LEFT JOIN instructors i ON c.instructor_id = i.instructor_id
JOIN fitness_disciplines fd ON w.discipline_id = fd.discipline_id
WHERE fd.name = 'Cycling' AND w.length_minutes = 20
ORDER BY calories_burned DESC
LIMIT 1;

-- Average metrics over time (3-month rolling average)
SELECT 
    strftime('%Y-%m', workout_timestamp) as month,
    ROUND(AVG(avg_watts), 1) as avg_watts,
    ROUND(AVG(avg_cadence_rpm), 1) as avg_cadence,
    ROUND(AVG(avg_resistance), 3) as avg_resistance,
    ROUND(AVG(calories_burned), 1) as avg_calories
FROM workouts w
JOIN fitness_disciplines fd ON w.discipline_id = fd.discipline_id
WHERE fd.name = 'Cycling' 
    AND w.avg_watts IS NOT NULL
    AND w.length_minutes >= 20
GROUP BY month
ORDER BY month;

-- ============================================================
-- JUST RIDE ANALYSIS
-- ============================================================

-- Just Ride statistics
SELECT 
    COUNT(*) as just_ride_count,
    SUM(calories_burned) as total_calories,
    ROUND(AVG(length_minutes), 1) as avg_length,
    ROUND(AVG(calories_burned), 1) as avg_calories,
    ROUND(SUM(distance_km), 2) as total_distance
FROM workouts
WHERE class_id IS NULL;

-- Just Ride vs Class comparison
SELECT 
    CASE 
        WHEN class_id IS NULL THEN 'Just Ride'
        ELSE 'Class'
    END as workout_type,
    COUNT(*) as workout_count,
    ROUND(AVG(calories_burned), 1) as avg_calories,
    ROUND(AVG(avg_watts), 1) as avg_watts,
    ROUND(AVG(distance_km), 2) as avg_distance
FROM workouts w
JOIN fitness_disciplines fd ON w.discipline_id = fd.discipline_id
WHERE fd.name = 'Cycling'
GROUP BY workout_type;

-- ============================================================
-- LIVE VS ON DEMAND
-- ============================================================

-- Live vs On Demand comparison
SELECT 
    CASE 
        WHEN is_live = 1 THEN 'Live'
        WHEN is_live = 0 THEN 'On Demand'
    END as class_type,
    COUNT(*) as workout_count,
    ROUND(AVG(calories_burned), 1) as avg_calories,
    ROUND(SUM(calories_burned), 0) as total_calories
FROM workouts
WHERE is_live IS NOT NULL
GROUP BY is_live;

-- ============================================================
-- RECENT ACTIVITY
-- ============================================================

-- Last 30 days summary
SELECT 
    COUNT(*) as total_workouts,
    COUNT(DISTINCT date(workout_timestamp)) as days_worked_out,
    SUM(calories_burned) as total_calories,
    ROUND(SUM(distance_km), 2) as total_distance_km,
    SUM(length_minutes) as total_minutes
FROM workouts
WHERE workout_timestamp >= datetime('now', '-30 days');

-- Last 10 workouts
SELECT 
    datetime(w.workout_timestamp) as when_taken,
    ct.title,
    i.name as instructor,
    w.length_minutes || ' min' as duration,
    w.calories_burned as calories,
    ROUND(w.distance_km, 2) || ' km' as distance
FROM workouts w
LEFT JOIN classes c ON w.class_id = c.class_id
LEFT JOIN class_titles ct ON c.title_id = ct.title_id
LEFT JOIN instructors i ON c.instructor_id = i.instructor_id
ORDER BY w.workout_timestamp DESC
LIMIT 10;