User-Generated Video Platform

Objective

Create a user-generated content video platform with upload management, moderation, and engagement tracking. This pattern supports social video platforms and community-driven content sites.

Step 1: Create Users Table

Create a table for content creators.

CREATE TABLE video_creators (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100),
    email VARCHAR(255) NOT NULL,
    avatar IMAGE(JPEG),
    bio TEXT,
    subscriber_count INTEGER DEFAULT 0,
    total_views INTEGER DEFAULT 0,
    video_count INTEGER DEFAULT 0,
    is_verified BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create User Videos Table

Create a table for user-uploaded videos.

CREATE TABLE user_videos (
    id INTEGER PRIMARY KEY,
    creator_id INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    video_file VIDEO(MP4),
    thumbnail IMAGE(JPEG),
    duration_seconds INTEGER,
    file_size BIGINT,
    resolution VARCHAR(20),
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    dislike_count INTEGER DEFAULT 0,
    comment_count INTEGER DEFAULT 0,
    share_count INTEGER DEFAULT 0,
    moderation_status VARCHAR(20) DEFAULT 'pending',
    visibility VARCHAR(20) DEFAULT 'public',
    is_monetized BOOLEAN DEFAULT FALSE,
    tags VARCHAR(500),
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    publish_date TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES video_creators(id)
);

Step 3: Create Moderation Table

Track video moderation history.

CREATE TABLE video_moderation (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    moderator_id VARCHAR(100),
    previous_status VARCHAR(20),
    new_status VARCHAR(20) NOT NULL,
    reason TEXT,
    notes TEXT,
    moderated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES user_videos(id)
);

Step 4: Create Engagement Table

Track video interactions.

CREATE TABLE video_engagement (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    engagement_type VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES user_videos(id)
);

Step 5: Insert Sample Creators

Add sample content creators.

INSERT INTO video_creators (id, username, display_name, email, bio, subscriber_count, total_views, video_count, is_verified) VALUES
    (1, 'techguru', 'Tech Guru', 'techguru@example.com', 'Tech reviews and tutorials', 150000, 5000000, 120, TRUE),
    (2, 'cookingqueen', 'Cooking Queen', 'cooking@example.com', 'Delicious recipes for everyone', 85000, 2500000, 200, TRUE),
    (3, 'gamerpro', 'Gamer Pro', 'gamer@example.com', 'Gaming walkthroughs and tips', 250000, 8000000, 350, TRUE),
    (4, 'diycraft', 'DIY Craft', 'diy@example.com', 'Creative DIY projects', 45000, 1200000, 80, FALSE),
    (5, 'fitnessfan', 'Fitness Fan', 'fitness@example.com', 'Home workout routines', 60000, 1800000, 95, FALSE);

Step 6: Insert User Videos

Add user-uploaded videos.

INSERT INTO user_videos (id, creator_id, title, description, duration_seconds, file_size, resolution, view_count, like_count, comment_count, moderation_status, visibility, tags, publish_date) VALUES
    (1, 1, 'iPhone 15 Review', 'Comprehensive review of the new iPhone', 1200, 524288000, '4K', 125000, 8500, 1200, 'approved', 'public', 'iphone,apple,review,tech', '2024-01-15 10:00:00'),
    (2, 1, 'Best Budget Laptops 2024', 'Top 5 affordable laptops', 900, 393216000, '1080p', 85000, 5200, 800, 'approved', 'public', 'laptop,budget,tech', '2024-01-20 14:00:00'),
    (3, 2, 'Easy Pasta Recipe', '15 minute pasta dish', 600, 262144000, '1080p', 95000, 7800, 450, 'approved', 'public', 'pasta,cooking,easy', '2024-01-18 12:00:00'),
    (4, 2, 'Chocolate Cake Tutorial', 'Perfect chocolate cake', 1500, 655360000, '1080p', 120000, 9500, 680, 'approved', 'public', 'cake,chocolate,baking', '2024-01-22 09:00:00'),
    (5, 3, 'Game Walkthrough Part 1', 'Complete first chapter', 3600, 1572864000, '4K', 250000, 15000, 2500, 'approved', 'public', 'gaming,walkthrough', '2024-01-10 16:00:00'),
    (6, 3, 'Pro Gaming Tips', 'Improve your skills', 900, 393216000, '1080p', 180000, 12000, 1800, 'approved', 'public', 'gaming,tips,tutorial', '2024-01-25 18:00:00'),
    (7, 4, 'DIY Home Decor', 'Budget home decoration', 1200, 524288000, '1080p', 45000, 3200, 320, 'approved', 'public', 'diy,decor,home', '2024-01-28 11:00:00'),
    (8, 5, '30 Min Full Body Workout', 'No equipment needed', 1800, 786432000, '1080p', 75000, 5500, 420, 'approved', 'public', 'workout,fitness,home', '2024-01-30 07:00:00'),
    (9, 1, 'Unreleased Product Leak', 'Leaked specifications', 600, 262144000, '1080p', 0, 0, 0, 'rejected', 'private', 'leak,unreleased', NULL),
    (10, 4, 'New Craft Project', 'Coming soon preview', 300, 131072000, '1080p', 0, 0, 0, 'pending', 'unlisted', 'craft,preview', NULL);

Step 7: Insert Moderation Records

Add moderation history.

INSERT INTO video_moderation (id, video_id, moderator_id, previous_status, new_status, reason, moderated_at) VALUES
    (1, 1, 'mod_admin', 'pending', 'approved', 'Content meets guidelines', '2024-01-15 09:30:00'),
    (2, 2, 'mod_admin', 'pending', 'approved', 'Content meets guidelines', '2024-01-20 13:30:00'),
    (3, 9, 'mod_senior', 'pending', 'rejected', 'Violates confidential information policy', '2024-01-27 15:00:00');

Step 8: Get Creator Dashboard

Display creator statistics.

SELECT
    vc.display_name,
    vc.subscriber_count,
    vc.video_count,
    COUNT(uv.id) as published_videos,
    SUM(uv.view_count) as total_views,
    SUM(uv.like_count) as total_likes
FROM video_creators vc
LEFT JOIN user_videos uv ON vc.id = uv.creator_id AND uv.moderation_status = 'approved'
GROUP BY vc.id, vc.display_name, vc.subscriber_count, vc.video_count
ORDER BY total_views DESC;

Step 9: Trending Videos

Get trending videos by engagement.

SELECT
    uv.title,
    vc.display_name as creator,
    uv.view_count,
    uv.like_count,
    uv.comment_count,
    CAST(uv.like_count AS DECIMAL) / NULLIF(uv.view_count, 0) * 100 as like_rate
FROM user_videos uv
INNER JOIN video_creators vc ON uv.creator_id = vc.id
WHERE uv.moderation_status = 'approved'
  AND uv.visibility = 'public'
ORDER BY uv.view_count DESC
LIMIT 10;

Step 10: Moderation Queue

Get videos pending review.

SELECT
    uv.id,
    uv.title,
    vc.username as creator,
    uv.duration_seconds / 60 as minutes,
    uv.upload_date,
    uv.tags
FROM user_videos uv
INNER JOIN video_creators vc ON uv.creator_id = vc.id
WHERE uv.moderation_status = 'pending'
ORDER BY uv.upload_date;

Step 11: Creator Performance

Analyze creator engagement rates.

SELECT
    vc.display_name,
    COUNT(uv.id) as videos,
    AVG(uv.view_count) as avg_views,
    AVG(uv.like_count) as avg_likes,
    AVG(CAST(uv.like_count AS DECIMAL) / NULLIF(uv.view_count, 0) * 100) as avg_like_rate
FROM video_creators vc
INNER JOIN user_videos uv ON vc.id = uv.creator_id
WHERE uv.moderation_status = 'approved'
GROUP BY vc.id, vc.display_name
ORDER BY avg_views DESC;

Step 12: Video Search by Tags

Search videos using tags.

SELECT
    uv.title,
    vc.display_name as creator,
    uv.view_count,
    uv.duration_seconds / 60 as minutes,
    uv.tags
FROM user_videos uv
INNER JOIN video_creators vc ON uv.creator_id = vc.id
WHERE uv.moderation_status = 'approved'
  AND uv.visibility = 'public'
  AND uv.tags LIKE '%tech%'
ORDER BY uv.view_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS video_engagement;
DROP TABLE IF EXISTS video_moderation;
DROP TABLE IF EXISTS user_videos;
DROP TABLE IF EXISTS video_creators;

Expected Outcomes

• User uploads tracked
• Moderation workflow works
• Engagement metrics captured
• Creator analytics available
• Search functionality enabled

Moderation Status

Key Concepts Learned

• User-generated content management
• Moderation workflows
• Engagement tracking
• Creator analytics
• Content discovery