🚀 SQL Project Series #33

YouTube Channel Analytics 📺

Analyze videos, creators, views, watch time, engagement, and subscriber growth using SQL to understand content performance and audience behavior.

🎯 Business Objectives

✅ Analyze video performance
✅ Track subscriber growth
✅ Measure audience engagement
✅ Identify top-performing content
✅ Compare video categories
✅ Analyze watch time
✅ Identify high-performing creators
✅ Discover the best publishing times

📂 Step 1: Create Database

CREATE DATABASE youtube_analytics_db;
USE youtube_analytics_db;

📂 Step 2: Create Channels Table

CREATE TABLE channels (
    channel_id INT PRIMARY KEY,
    channel_name VARCHAR(100),
    category VARCHAR(50),
    country VARCHAR(50),
    created_date DATE
);

📂 Step 3: Create Videos Table

CREATE TABLE videos (
    video_id INT PRIMARY KEY,
    channel_id INT,
    video_title VARCHAR(200),
    category VARCHAR(50),
    publish_date DATETIME,
    duration_minutes DECIMAL(6,2),
    views BIGINT,
    likes INT,
    comments INT,
    shares INT,
    FOREIGN KEY (channel_id)
        REFERENCES channels(channel_id)
);

📂 Step 4: Create Subscribers Table

CREATE TABLE subscribers (
    subscriber_id INT PRIMARY KEY,
    channel_id INT,
    subscribe_date DATE,
    unsubscribe_date DATE,
    FOREIGN KEY (channel_id)
        REFERENCES channels(channel_id)
);

📂 Step 5: Insert Sample Channels

INSERT INTO channels VALUES
(1,'Data Simplifier','Education','India','2023-01-10'),
(2,'Tech World','Technology','India','2022-08-15'),
(3,'Finance Explained','Finance','USA','2021-05-20'),
(4,'Travel Diaries','Travel','India','2023-04-12');

📂 Step 6: Insert Sample Videos

INSERT INTO videos VALUES
(101,1,'SQL Interview Questions','Education','2025-01-05 10:00:00',12,15000,900,120,80),
(102,1,'Power BI Dashboard Tutorial','Education','2025-01-08 18:00:00',18,22000,1400,180,150),
(103,2,'Best AI Tools','Technology','2025-01-10 12:00:00',10,35000,2800,310,420),
(104,3,'How to Invest','Finance','2025-01-12 09:00:00',15,28000,2100,250,300),
(105,4,'Top Places in India','Travel','2025-01-15 20:00:00',14,19000,1300,170,200);

📂 Step 7: Insert Sample Subscribers

INSERT INTO subscribers VALUES
(1001,1,'2025-01-01',NULL),
(1002,1,'2025-01-03',NULL),
(1003,1,'2025-01-05','2025-03-01'),
(1004,2,'2025-01-02',NULL),
(1005,3,'2025-01-04',NULL),
(1006,4,'2025-01-10',NULL);

🧠 SQL Concepts You'll Practice

✔ Joins
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ CTEs
✔ Subqueries
✔ Window Functions
✔ Ranking
✔ Date & Time Functions
✔ Conditional Aggregation

📊 Business KPIs You Can Build

📈 Total Channels
📈 Total Videos
📈 Total Views
📈 Total Likes
📈 Total Comments
📈 Total Shares
📈 Total Subscribers
📈 Subscriber Growth Rate
📈 Subscriber Churn Rate
📈 Average Views per Video
📈 Average Likes per Video
📈 Average Comments per Video
📈 Engagement Rate
📈 Like-to-View Ratio
📈 Comment-to-View Ratio
📈 Share-to-View Ratio
📈 Watch Time
📈 Average Video Duration
📈 Top 10 Videos by Views
📈 Top Videos by Engagement
📈 Top Performing Categories
📈 Channel-wise Performance
📈 Views by Publishing Day
📈 Views by Publishing Hour
📈 Monthly Views Growth
📈 Subscriber Growth by Month
📈 Content Performance Dashboard

💡 Example 1: Find Top 5 Videos by Views

SELECT
    video_title,
    views
FROM videos
ORDER BY views DESC
LIMIT 5;

💡 Example 2: Calculate Engagement Rate

SELECT
    video_title,
    views,
    likes,
    comments,
    shares,
    ROUND(
        100.0 * (likes + comments + shares) / NULLIF(views, 0),
        2
    ) AS engagement_rate
FROM videos
ORDER BY engagement_rate DESC;

💡 Example 3: Find Top Video in Each Category