Video Duration Query

Objective

Learn to query and analyze videos by duration. This enables content planning, scheduling optimization, and understanding content length distribution.

Step 1: Create Video Content Table

Create a table for videos with duration tracking.

CREATE TABLE video_content (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    category VARCHAR(50),
    duration_seconds INTEGER NOT NULL,
    content_type VARCHAR(50),
    creator VARCHAR(100),
    is_published BOOLEAN DEFAULT FALSE,
    publish_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Videos

Add videos with various durations.

INSERT INTO video_content (id, title, category, duration_seconds, content_type, creator, is_published, publish_date) VALUES
    (1, 'Quick Tip: Keyboard Shortcuts', 'tips', 45, 'short', 'tips_team', TRUE, '2024-01-10'),
    (2, 'Product Feature Overview', 'product', 180, 'standard', 'product_team', TRUE, '2024-01-12'),
    (3, 'Getting Started Guide', 'tutorial', 600, 'tutorial', 'education', TRUE, '2024-01-15'),
    (4, 'Deep Dive: Architecture', 'technical', 2700, 'long-form', 'engineering', TRUE, '2024-01-18'),
    (5, 'Quick Tip: Search Functions', 'tips', 60, 'short', 'tips_team', TRUE, '2024-01-20'),
    (6, 'Webinar: Best Practices', 'webinar', 3600, 'long-form', 'training', TRUE, '2024-01-22'),
    (7, 'Feature Demo: Reports', 'product', 300, 'standard', 'product_team', TRUE, '2024-01-25'),
    (8, 'Interview: Customer Success', 'testimonial', 900, 'interview', 'marketing', TRUE, '2024-01-28'),
    (9, 'Conference Keynote', 'event', 5400, 'long-form', 'executive', TRUE, '2024-02-01'),
    (10, 'Quick Tip: Data Export', 'tips', 90, 'short', 'tips_team', FALSE, NULL),
    (11, 'Tutorial: Advanced Queries', 'tutorial', 1200, 'tutorial', 'education', TRUE, '2024-02-05'),
    (12, 'Product Update February', 'product', 420, 'standard', 'product_team', TRUE, '2024-02-08');

Step 3: Format Duration Display

Query videos with formatted duration.

SELECT
    title,
    duration_seconds,
    duration_seconds / 60 as minutes,
    duration_seconds % 60 as remaining_seconds,
    category
FROM video_content
ORDER BY duration_seconds DESC;

Step 4: Duration Categories

Classify videos by length.

SELECT
    title,
    duration_seconds,
    CASE
        WHEN duration_seconds < 120 THEN 'Short (< 2 min)'
        WHEN duration_seconds < 600 THEN 'Medium (2-10 min)'
        WHEN duration_seconds < 1800 THEN 'Standard (10-30 min)'
        WHEN duration_seconds < 3600 THEN 'Long (30-60 min)'
        ELSE 'Extended (> 1 hour)'
    END as duration_category
FROM video_content
ORDER BY duration_seconds;

Step 5: Count by Duration Range

Count videos in each duration range.

SELECT
    CASE
        WHEN duration_seconds < 120 THEN 'Short (< 2 min)'
        WHEN duration_seconds < 600 THEN 'Medium (2-10 min)'
        WHEN duration_seconds < 1800 THEN 'Standard (10-30 min)'
        WHEN duration_seconds < 3600 THEN 'Long (30-60 min)'
        ELSE 'Extended (> 1 hour)'
    END as duration_range,
    COUNT(*) as video_count,
    SUM(duration_seconds) / 60 as total_minutes
FROM video_content
GROUP BY
    CASE
        WHEN duration_seconds < 120 THEN 'Short (< 2 min)'
        WHEN duration_seconds < 600 THEN 'Medium (2-10 min)'
        WHEN duration_seconds < 1800 THEN 'Standard (10-30 min)'
        WHEN duration_seconds < 3600 THEN 'Long (30-60 min)'
        ELSE 'Extended (> 1 hour)'
    END
ORDER BY video_count DESC;

Step 6: Find Videos by Duration Range

Query videos within specific duration.

SELECT
    title,
    category,
    duration_seconds / 60 as minutes,
    creator
FROM video_content
WHERE duration_seconds BETWEEN 300 AND 900
ORDER BY duration_seconds;

Step 7: Average Duration by Category

Calculate average duration per category.

SELECT
    category,
    COUNT(*) as video_count,
    AVG(duration_seconds) / 60 as avg_minutes,
    MIN(duration_seconds) / 60 as min_minutes,
    MAX(duration_seconds) / 60 as max_minutes
FROM video_content
GROUP BY category
ORDER BY avg_minutes DESC;

Step 8: Total Content Duration

Calculate total content library duration.

SELECT
    COUNT(*) as total_videos,
    SUM(duration_seconds) as total_seconds,
    SUM(duration_seconds) / 60 as total_minutes,
    SUM(duration_seconds) / 3600 as total_hours
FROM video_content
WHERE is_published = TRUE;

Step 9: Duration by Content Type

Analyze duration distribution by type.

SELECT
    content_type,
    COUNT(*) as count,
    AVG(duration_seconds) / 60 as avg_minutes,
    SUM(duration_seconds) / 60 as total_minutes
FROM video_content
GROUP BY content_type
ORDER BY avg_minutes DESC;

Step 10: Videos for Time Slot

Find videos that fit a time slot.

SELECT
    title,
    category,
    duration_seconds / 60 as minutes,
    content_type
FROM video_content
WHERE duration_seconds <= 600
  AND is_published = TRUE
ORDER BY duration_seconds DESC;

Step 11: Creator Productivity

Analyze content duration by creator.

SELECT
    creator,
    COUNT(*) as video_count,
    SUM(duration_seconds) / 3600 as total_hours,
    AVG(duration_seconds) / 60 as avg_minutes
FROM video_content
GROUP BY creator
ORDER BY total_hours DESC;

Step 12: Monthly Content Duration

Track duration added per month.

SELECT
    publish_date,
    COUNT(*) as videos_published,
    SUM(duration_seconds) / 60 as minutes_added
FROM video_content
WHERE is_published = TRUE
GROUP BY publish_date
ORDER BY publish_date;

Cleanup (Optional)

DROP TABLE IF EXISTS video_content;

Expected Outcomes

• Duration calculations work
• Category classification functions
• Range queries return correct results
• Statistics aggregated properly
• Time-based filtering works

Duration Categories

Key Concepts Learned

• Duration-based queries
• Time formatting in SQL
• CASE expressions for ranges
• Duration aggregations
• Content scheduling queries