Product Video Demos

Objective

Create a system for managing product demonstration videos with feature timestamps and highlights. This enables enhanced product understanding and improved customer engagement.

Step 1: Create Products Table

Create a table for products with video support.

CREATE TABLE demo_products (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    has_demo_video BOOLEAN DEFAULT FALSE,
    demo_video_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Demo Videos Table

Create a table for product demos.

CREATE TABLE product_demos (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    video_file VIDEO(MP4),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    video_type VARCHAR(50) DEFAULT 'demo',
    duration_seconds INTEGER,
    thumbnail IMAGE(JPEG),
    resolution VARCHAR(20),
    view_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_published BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES demo_products(id)
);

Step 3: Create Feature Highlights Table

Store video timestamps for features.

CREATE TABLE demo_highlights (
    id INTEGER PRIMARY KEY,
    demo_id INTEGER NOT NULL,
    highlight_title VARCHAR(255) NOT NULL,
    start_time_seconds INTEGER NOT NULL,
    end_time_seconds INTEGER,
    description TEXT,
    feature_category VARCHAR(50),
    sort_order INTEGER,
    FOREIGN KEY (demo_id) REFERENCES product_demos(id)
);

Step 4: Insert Sample Products

Add sample products.

INSERT INTO demo_products (id, sku, name, category) VALUES
    (1, 'LAPTOP-PRO', 'ProBook Laptop 15', 'Electronics'),
    (2, 'CAMERA-DSLR', 'PhotoMax DSLR', 'Photography'),
    (3, 'HEADPHONES-BT', 'SoundWave Headphones', 'Audio'),
    (4, 'SMARTWATCH-FIT', 'FitTrack Watch', 'Wearables'),
    (5, 'DRONE-PRO', 'AerialPro Drone', 'Electronics');

Step 5: Insert Demo Videos

Add product demonstration videos.

INSERT INTO product_demos (id, product_id, title, description, video_type, duration_seconds, resolution, view_count, is_featured, is_published) VALUES
    -- Laptop demos
    (1, 1, 'ProBook Laptop - Complete Overview', 'Full product tour and feature demonstration', 'overview', 480, '1080p', 15000, TRUE, TRUE),
    (2, 1, 'ProBook Laptop - Performance Test', 'Benchmark tests and real-world performance', 'performance', 360, '1080p', 8500, FALSE, TRUE),
    (3, 1, 'ProBook Laptop - Unboxing', 'Unboxing and first impressions', 'unboxing', 300, '1080p', 12000, FALSE, TRUE),
    -- Camera demos
    (4, 2, 'PhotoMax DSLR - Camera Features', 'Complete camera feature walkthrough', 'overview', 600, '4K', 22000, TRUE, TRUE),
    (5, 2, 'PhotoMax DSLR - Photo Samples', 'Sample photos and video footage', 'samples', 420, '4K', 18000, FALSE, TRUE),
    -- Headphones demos
    (6, 3, 'SoundWave - Sound Quality Test', 'Audio quality demonstration', 'audio_test', 240, '1080p', 9500, TRUE, TRUE),
    (7, 3, 'SoundWave - Noise Canceling Demo', 'Active noise canceling showcase', 'feature', 180, '1080p', 7200, FALSE, TRUE),
    -- Smartwatch demos
    (8, 4, 'FitTrack Watch - Fitness Features', 'Health and fitness tracking demo', 'overview', 540, '1080p', 11000, TRUE, TRUE),
    -- Drone demos
    (9, 5, 'AerialPro Drone - Flight Demo', 'First flight and controls tutorial', 'overview', 720, '4K', 25000, TRUE, TRUE),
    (10, 5, 'AerialPro Drone - Camera Quality', 'Aerial footage samples', 'samples', 480, '4K', 19000, FALSE, TRUE);

Step 6: Insert Feature Highlights

Add timestamp highlights.

INSERT INTO demo_highlights (id, demo_id, highlight_title, start_time_seconds, end_time_seconds, description, feature_category, sort_order) VALUES
    -- Laptop overview highlights
    (1, 1, 'Display Quality', 30, 90, 'High-resolution display showcase', 'display', 1),
    (2, 1, 'Keyboard and Trackpad', 90, 150, 'Input device demonstration', 'input', 2),
    (3, 1, 'Port Selection', 150, 200, 'Available ports and connectivity', 'ports', 3),
    (4, 1, 'Battery Life', 200, 280, 'Battery performance and charging', 'battery', 4),
    (5, 1, 'Software Features', 280, 400, 'Pre-installed software overview', 'software', 5),
    (6, 1, 'Summary', 400, 480, 'Final thoughts and recommendations', 'summary', 6),
    -- Camera overview highlights
    (7, 4, 'Body and Controls', 30, 120, 'Camera body tour and controls', 'hardware', 1),
    (8, 4, 'Autofocus System', 120, 240, 'AF performance demonstration', 'autofocus', 2),
    (9, 4, 'Video Recording', 240, 360, '4K video capabilities', 'video', 3),
    (10, 4, 'Low Light Performance', 360, 480, 'Night photography results', 'low_light', 4),
    (11, 4, 'Connectivity', 480, 540, 'WiFi and app integration', 'connectivity', 5),
    -- Drone flight demo highlights
    (12, 9, 'Pre-flight Setup', 0, 120, 'Unfolding and calibration', 'setup', 1),
    (13, 9, 'Basic Flight Controls', 120, 300, 'Takeoff and basic maneuvers', 'controls', 2),
    (14, 9, 'Camera Controls', 300, 450, 'Gimbal and camera settings', 'camera', 3),
    (15, 9, 'Intelligent Flight Modes', 450, 600, 'Automated flight features', 'automation', 4),
    (16, 9, 'Return to Home', 600, 720, 'Safety features demonstration', 'safety', 5);

Step 7: Update Product Video Counts

Calculate videos per product.

UPDATE demo_products
SET has_demo_video = TRUE,
    demo_video_count = (
        SELECT COUNT(*) FROM product_demos
        WHERE product_demos.product_id = demo_products.id
          AND product_demos.is_published = TRUE
    )
WHERE id IN (SELECT DISTINCT product_id FROM product_demos);

SELECT name, has_demo_video, demo_video_count FROM demo_products;

Step 8: Get Product Demo Gallery

List all demos for a product.

SELECT
    pd.title,
    pd.video_type,
    pd.duration_seconds / 60 as minutes,
    pd.resolution,
    pd.view_count,
    pd.is_featured
FROM product_demos pd
WHERE pd.product_id = 1
  AND pd.is_published = TRUE
ORDER BY pd.is_featured DESC, pd.view_count DESC;

Step 9: Get Demo with Highlights

View demo video with chapter markers.

SELECT
    pd.title as video_title,
    dh.highlight_title as chapter,
    dh.start_time_seconds as start_sec,
    dh.description,
    dh.feature_category
FROM product_demos pd
INNER JOIN demo_highlights dh ON pd.id = dh.demo_id
WHERE pd.id = 1
ORDER BY dh.sort_order;

Step 10: Popular Demos

Get most viewed demonstration videos.

SELECT
    dp.name as product,
    pd.title,
    pd.video_type,
    pd.view_count,
    pd.resolution
FROM product_demos pd
INNER JOIN demo_products dp ON pd.product_id = dp.id
WHERE pd.is_published = TRUE
ORDER BY pd.view_count DESC
LIMIT 10;

Step 11: Demo Coverage Report

Analyze demo coverage by category.

SELECT
    dp.category,
    COUNT(DISTINCT dp.id) as total_products,
    COUNT(DISTINCT CASE WHEN dp.has_demo_video THEN dp.id END) as with_demos,
    SUM(pd.view_count) as total_views,
    AVG(pd.duration_seconds) / 60 as avg_duration_min
FROM demo_products dp
LEFT JOIN product_demos pd ON dp.id = pd.product_id
GROUP BY dp.category
ORDER BY total_products DESC;

Step 12: Feature Category Analysis

Analyze highlighted features.

SELECT
    dh.feature_category,
    COUNT(*) as highlight_count,
    AVG(dh.end_time_seconds - dh.start_time_seconds) as avg_duration_sec
FROM demo_highlights dh
GROUP BY dh.feature_category
ORDER BY highlight_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS demo_highlights;
DROP TABLE IF EXISTS product_demos;
DROP TABLE IF EXISTS demo_products;

Expected Outcomes

• Demo videos linked to products
• Feature timestamps stored
• View counts tracked
• Coverage analyzed
• Popular content identified

Video Types

Key Concepts Learned

• Product video management
• Timestamp/chapter markers
• View analytics
• Coverage tracking
• Feature categorization