Video Resolution Variants

Objective

Create a system for storing multiple resolution variants of videos. This enables adaptive bitrate streaming, device-optimized delivery, and bandwidth-efficient video playback.

Step 1: Create Master Videos Table

Create a table for source videos.

CREATE TABLE master_videos (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    original_file VIDEO(MP4),
    original_resolution VARCHAR(20) NOT NULL,
    original_width INTEGER,
    original_height INTEGER,
    duration_seconds INTEGER NOT NULL,
    original_bitrate INTEGER,
    original_file_size BIGINT,
    encoding_status VARCHAR(20) DEFAULT 'pending',
    variant_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Video Variants Table

Create a table for resolution variants.

CREATE TABLE video_variants (
    id INTEGER PRIMARY KEY,
    master_id INTEGER NOT NULL,
    video_file VIDEO(MP4),
    resolution_label VARCHAR(20) NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    bitrate INTEGER NOT NULL,
    file_size BIGINT,
    codec VARCHAR(50) DEFAULT 'H.264',
    frame_rate DECIMAL(5, 2) DEFAULT 30.00,
    profile VARCHAR(50),
    encoding_status VARCHAR(20) DEFAULT 'pending',
    encoding_time_seconds INTEGER,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (master_id) REFERENCES master_videos(id)
);

Step 3: Create Resolution Presets Table

Define standard resolution presets.

CREATE TABLE resolution_presets (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    label VARCHAR(20) NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    target_bitrate INTEGER NOT NULL,
    min_source_height INTEGER,
    codec VARCHAR(50) DEFAULT 'H.264',
    profile VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE
);

INSERT INTO resolution_presets (id, name, label, width, height, target_bitrate, min_source_height, codec, profile) VALUES
    (1, '4K Ultra HD', '2160p', 3840, 2160, 15000000, 2160, 'H.265', 'main'),
    (2, '1080p Full HD', '1080p', 1920, 1080, 5000000, 1080, 'H.264', 'high'),
    (3, '720p HD', '720p', 1280, 720, 2500000, 720, 'H.264', 'main'),
    (4, '480p SD', '480p', 854, 480, 1000000, 480, 'H.264', 'main'),
    (5, '360p Low', '360p', 640, 360, 500000, 360, 'H.264', 'baseline'),
    (6, '240p Mobile', '240p', 426, 240, 250000, 0, 'H.264', 'baseline');

Step 4: Insert Master Videos

Add source videos.

INSERT INTO master_videos (id, title, original_resolution, original_width, original_height, duration_seconds, original_bitrate, original_file_size, encoding_status) VALUES
    (1, 'Product Launch Keynote', '4K', 3840, 2160, 3600, 25000000, 11250000000, 'completed'),
    (2, 'Tutorial: Getting Started', '1080p', 1920, 1080, 900, 8000000, 900000000, 'completed'),
    (3, 'Quick Tips Video', '1080p', 1920, 1080, 300, 6000000, 225000000, 'completed'),
    (4, 'Legacy Training Video', '720p', 1280, 720, 1800, 3000000, 675000000, 'completed'),
    (5, 'New Upload', '4K', 3840, 2160, 600, 20000000, 1500000000, 'processing');

Step 5: Insert Video Variants

Add resolution variants.

INSERT INTO video_variants (id, master_id, resolution_label, width, height, bitrate, file_size, codec, profile, encoding_status, encoding_time_seconds, is_default) VALUES
    -- Product Launch Keynote variants
    (1, 1, '2160p', 3840, 2160, 15000000, 6750000000, 'H.265', 'main', 'completed', 1200, FALSE),
    (2, 1, '1080p', 1920, 1080, 5000000, 2250000000, 'H.264', 'high', 'completed', 480, TRUE),
    (3, 1, '720p', 1280, 720, 2500000, 1125000000, 'H.264', 'main', 'completed', 360, FALSE),
    (4, 1, '480p', 854, 480, 1000000, 450000000, 'H.264', 'main', 'completed', 240, FALSE),
    (5, 1, '360p', 640, 360, 500000, 225000000, 'H.264', 'baseline', 'completed', 180, FALSE),
    -- Tutorial variants
    (6, 2, '1080p', 1920, 1080, 5000000, 562500000, 'H.264', 'high', 'completed', 120, TRUE),
    (7, 2, '720p', 1280, 720, 2500000, 281250000, 'H.264', 'main', 'completed', 90, FALSE),
    (8, 2, '480p', 854, 480, 1000000, 112500000, 'H.264', 'main', 'completed', 60, FALSE),
    (9, 2, '360p', 640, 360, 500000, 56250000, 'H.264', 'baseline', 'completed', 45, FALSE),
    -- Quick Tips variants
    (10, 3, '1080p', 1920, 1080, 5000000, 187500000, 'H.264', 'high', 'completed', 40, TRUE),
    (11, 3, '720p', 1280, 720, 2500000, 93750000, 'H.264', 'main', 'completed', 30, FALSE),
    (12, 3, '480p', 854, 480, 1000000, 37500000, 'H.264', 'main', 'completed', 20, FALSE),
    -- Legacy Training (max 720p)
    (13, 4, '720p', 1280, 720, 2500000, 562500000, 'H.264', 'main', 'completed', 240, TRUE),
    (14, 4, '480p', 854, 480, 1000000, 225000000, 'H.264', 'main', 'completed', 180, FALSE),
    (15, 4, '360p', 640, 360, 500000, 112500000, 'H.264', 'baseline', 'completed', 120, FALSE);

Step 6: Update Master Statistics

Calculate variant counts.

UPDATE master_videos
SET variant_count = (
    SELECT COUNT(*) FROM video_variants
    WHERE video_variants.master_id = master_videos.id
      AND video_variants.encoding_status = 'completed'
);

SELECT title, original_resolution, variant_count FROM master_videos;

Step 7: Get Available Resolutions

List variants for a video.

SELECT
    mv.title,
    vv.resolution_label,
    vv.width || 'x' || vv.height as dimensions,
    vv.bitrate / 1000000 as bitrate_mbps,
    vv.file_size / 1048576 as size_mb,
    vv.codec,
    vv.is_default
FROM master_videos mv
INNER JOIN video_variants vv ON mv.id = vv.master_id
WHERE mv.id = 1
  AND vv.encoding_status = 'completed'
ORDER BY vv.height DESC;

Step 8: Adaptive Streaming Manifest

Generate quality ladder for ABR.

SELECT
    resolution_label as quality,
    width,
    height,
    bitrate,
    bitrate / 1000 as bandwidth_kbps,
    codec
FROM video_variants
WHERE master_id = 1
  AND encoding_status = 'completed'
ORDER BY bitrate DESC;

Step 9: Storage Analysis

Analyze storage usage by resolution.

SELECT
    vv.resolution_label,
    COUNT(*) as variant_count,
    SUM(vv.file_size) / 1073741824 as total_gb,
    AVG(vv.bitrate) / 1000000 as avg_bitrate_mbps,
    AVG(vv.file_size) / 1048576 as avg_size_mb
FROM video_variants vv
WHERE vv.encoding_status = 'completed'
GROUP BY vv.resolution_label
ORDER BY AVG(vv.height) DESC;

Step 10: Encoding Efficiency

Analyze encoding performance.

SELECT
    mv.title,
    mv.duration_seconds as video_duration,
    SUM(vv.encoding_time_seconds) as total_encoding_time,
    SUM(vv.encoding_time_seconds) / mv.duration_seconds as encoding_ratio,
    COUNT(vv.id) as variants_created
FROM master_videos mv
INNER JOIN video_variants vv ON mv.id = vv.master_id
WHERE vv.encoding_status = 'completed'
GROUP BY mv.id, mv.title, mv.duration_seconds
ORDER BY encoding_ratio DESC;

Step 11: Missing Variants

Find videos missing expected resolutions.

SELECT
    mv.title,
    mv.original_resolution,
    COUNT(vv.id) as current_variants,
    CASE
        WHEN mv.original_height >= 2160 THEN 5
        WHEN mv.original_height >= 1080 THEN 4
        WHEN mv.original_height >= 720 THEN 3
        ELSE 2
    END as expected_variants
FROM master_videos mv
LEFT JOIN video_variants vv ON mv.id = vv.master_id AND vv.encoding_status = 'completed'
GROUP BY mv.id, mv.title, mv.original_resolution, mv.original_height
HAVING COUNT(vv.id) < CASE
    WHEN mv.original_height >= 2160 THEN 5
    WHEN mv.original_height >= 1080 THEN 4
    WHEN mv.original_height >= 720 THEN 3
    ELSE 2
END;

Step 12: Bandwidth Recommendations

Suggest quality based on bandwidth.

SELECT
    resolution_label,
    bitrate / 1000 as required_kbps,
    CASE
        WHEN bitrate <= 500000 THEN 'Mobile 3G'
        WHEN bitrate <= 1500000 THEN 'Mobile 4G'
        WHEN bitrate <= 3000000 THEN 'Broadband'
        WHEN bitrate <= 8000000 THEN 'Fast Broadband'
        ELSE 'Fiber'
    END as recommended_connection
FROM video_variants
WHERE master_id = 1
  AND encoding_status = 'completed'
ORDER BY bitrate;

Cleanup (Optional)

DROP TABLE IF EXISTS video_variants;
DROP TABLE IF EXISTS resolution_presets;
DROP TABLE IF EXISTS master_videos;

Expected Outcomes

• Multiple resolutions stored
• Adaptive streaming enabled
• Storage optimized by quality
• Encoding tracked
• Bandwidth recommendations work

Resolution Ladder

Key Concepts Learned

• Multi-resolution video storage
• Adaptive bitrate streaming
• Encoding pipeline management
• Storage optimization
• Bandwidth-based quality selection