Video Thumbnail Storage

Objective

Store and manage multiple thumbnail options for each video. This enables A/B testing thumbnails, device-optimized images, and improved video discovery.

Step 1: Create Video Table

Create a table for videos with thumbnail metadata.

CREATE TABLE video_with_thumbnails (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    duration_seconds INTEGER,
    primary_thumbnail_id INTEGER,
    thumbnail_count INTEGER DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Thumbnails Table

Create a table for thumbnail images.

CREATE TABLE video_thumbnails (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    thumbnail_image IMAGE(JPEG),
    thumbnail_type VARCHAR(20) DEFAULT 'auto',
    source_timestamp DECIMAL(10, 3),
    width INTEGER,
    height INTEGER,
    file_size INTEGER,
    is_primary BOOLEAN DEFAULT FALSE,
    is_approved BOOLEAN DEFAULT TRUE,
    click_rate DECIMAL(5, 4),
    impressions INTEGER DEFAULT 0,
    clicks INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES video_with_thumbnails(id)
);

Step 3: Insert Sample Videos

Add sample video records.

INSERT INTO video_with_thumbnails (id, title, duration_seconds, thumbnail_count, category) VALUES
    (1, 'Product Launch Event', 1800, 5, 'marketing'),
    (2, 'Tutorial: Getting Started', 900, 4, 'education'),
    (3, 'Customer Success Story', 600, 3, 'testimonial'),
    (4, 'Behind the Scenes', 1200, 4, 'entertainment'),
    (5, 'Tech Review', 1500, 5, 'review');

Step 4: Insert Thumbnails

Add thumbnail records for videos.

INSERT INTO video_thumbnails (id, video_id, thumbnail_type, source_timestamp, width, height, file_size, is_primary, click_rate, impressions, clicks) VALUES
    -- Product Launch thumbnails
    (1, 1, 'auto', 30.000, 1920, 1080, 245760, FALSE, 0.0312, 50000, 1560),
    (2, 1, 'auto', 120.500, 1920, 1080, 312000, TRUE, 0.0425, 50000, 2125),
    (3, 1, 'custom', NULL, 1920, 1080, 287500, FALSE, 0.0387, 50000, 1935),
    (4, 1, 'ai_generated', NULL, 1920, 1080, 195000, FALSE, 0.0298, 50000, 1490),
    (5, 1, 'branded', NULL, 1920, 1080, 325000, FALSE, 0.0456, 50000, 2280),
    -- Tutorial thumbnails
    (6, 2, 'auto', 15.000, 1280, 720, 153600, FALSE, 0.0234, 30000, 702),
    (7, 2, 'auto', 180.000, 1280, 720, 168000, TRUE, 0.0356, 30000, 1068),
    (8, 2, 'custom', NULL, 1280, 720, 145000, FALSE, 0.0312, 30000, 936),
    (9, 2, 'branded', NULL, 1280, 720, 198000, FALSE, 0.0278, 30000, 834),
    -- Customer Story thumbnails
    (10, 3, 'auto', 45.000, 1920, 1080, 234000, FALSE, 0.0398, 20000, 796),
    (11, 3, 'auto', 300.000, 1920, 1080, 256000, TRUE, 0.0512, 20000, 1024),
    (12, 3, 'custom', NULL, 1920, 1080, 212000, FALSE, 0.0445, 20000, 890);

Step 5: Update Primary Thumbnail IDs

Set the primary thumbnail reference.

UPDATE video_with_thumbnails
SET primary_thumbnail_id = (
    SELECT id FROM video_thumbnails
    WHERE video_thumbnails.video_id = video_with_thumbnails.id
      AND is_primary = TRUE
    LIMIT 1
)
WHERE id IN (1, 2, 3);

SELECT id, title, primary_thumbnail_id FROM video_with_thumbnails;

Step 6: Get Video with Primary Thumbnail

Retrieve videos with their primary thumbnail.

SELECT
    v.title,
    v.category,
    vt.thumbnail_type,
    vt.width,
    vt.height,
    vt.file_size / 1024 as size_kb,
    vt.click_rate
FROM video_with_thumbnails v
INNER JOIN video_thumbnails vt ON v.primary_thumbnail_id = vt.id
ORDER BY vt.click_rate DESC;

Step 7: Get All Thumbnails for Video

Retrieve all thumbnail options for a video.

SELECT
    thumbnail_type,
    source_timestamp,
    width,
    height,
    file_size / 1024 as size_kb,
    is_primary,
    click_rate,
    impressions,
    clicks
FROM video_thumbnails
WHERE video_id = 1
ORDER BY click_rate DESC;

Step 8: Best Performing Thumbnails

Find top performing thumbnails by click rate.

SELECT
    v.title,
    vt.thumbnail_type,
    vt.click_rate,
    vt.clicks,
    vt.impressions,
    vt.is_primary
FROM video_with_thumbnails v
INNER JOIN video_thumbnails vt ON v.id = vt.video_id
WHERE vt.impressions > 10000
ORDER BY vt.click_rate DESC
LIMIT 10;

Step 9: Thumbnail Performance by Type

Analyze click rates by thumbnail type.

SELECT
    thumbnail_type,
    COUNT(*) as thumbnail_count,
    AVG(click_rate) as avg_click_rate,
    SUM(clicks) as total_clicks,
    SUM(impressions) as total_impressions
FROM video_thumbnails
WHERE impressions > 0
GROUP BY thumbnail_type
ORDER BY avg_click_rate DESC;

Step 10: Find Best Alternative Thumbnail

Find non-primary thumbnails that outperform primary.

SELECT
    v.title,
    vt.thumbnail_type,
    vt.click_rate as alternative_rate,
    primary_t.click_rate as primary_rate,
    vt.click_rate - primary_t.click_rate as improvement
FROM video_with_thumbnails v
INNER JOIN video_thumbnails vt ON v.id = vt.video_id
INNER JOIN video_thumbnails primary_t ON v.primary_thumbnail_id = primary_t.id
WHERE vt.is_primary = FALSE
  AND vt.click_rate > primary_t.click_rate
ORDER BY improvement DESC;

Step 11: Thumbnail Size Analysis

Analyze thumbnail file sizes.

SELECT
    v.category,
    COUNT(vt.id) as thumbnail_count,
    AVG(vt.file_size) / 1024 as avg_size_kb,
    SUM(vt.file_size) / 1048576 as total_mb
FROM video_with_thumbnails v
INNER JOIN video_thumbnails vt ON v.id = vt.video_id
GROUP BY v.category
ORDER BY total_mb DESC;

Step 12: Videos Needing Better Thumbnails

Find videos with low thumbnail performance.

SELECT
    v.title,
    v.category,
    MAX(vt.click_rate) as best_click_rate,
    AVG(vt.click_rate) as avg_click_rate,
    COUNT(vt.id) as thumbnail_options
FROM video_with_thumbnails v
INNER JOIN video_thumbnails vt ON v.id = vt.video_id
GROUP BY v.id, v.title, v.category
HAVING MAX(vt.click_rate) < 0.04
ORDER BY best_click_rate;

Cleanup (Optional)

DROP TABLE IF EXISTS video_thumbnails;
DROP TABLE IF EXISTS video_with_thumbnails;

Expected Outcomes

• Multiple thumbnails per video
• Click rate tracking works
• Performance comparison enabled
• Best performers identified
• Size optimization data available

Thumbnail Types

Key Concepts Learned

• Multiple thumbnail management
• Click rate tracking
• A/B testing thumbnails
• Performance comparison
• Storage optimization