SQL Programming Resources

🚀 𝟰 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗕𝗼𝗼𝘀𝘁 𝗬𝗼𝘂𝗿 𝗥𝗲𝘀𝘂𝗺𝗲 & 𝗖𝗼𝗻𝗳𝗶𝗱𝗲𝗻𝗰𝗲 🎓🔥

Make your resume stand out and feel more confident during your job search.

🚀 Build confidence and a career-focused mindset

✅ 100% FREE
✅ Beginner Friendly
✅ Improve Your Resume
✅ Develop Career-Ready Skills
✅ Great for Students, Freshers & Professionals

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4gce062

🔥 Don't just apply for jobs — build the skills and confidence to stand out!

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY employee_count DESC;

💡 Example 2: Average Salary by Department

SELECT d.department_name, ROUND(AVG(e.salary), 2) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY average_salary DESC;

💡 Example 3: Identify Highest-Paid Employees

SELECT employee_name, job_title, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

💡 Example 4: Calculate Attrition Rate

SELECT ROUND(100.0 * SUM(CASE WHEN employment_status = 'Resigned' THEN 1 ELSE 0 END) / COUNT(*), 2) AS attrition_rate
FROM employees;

💡 Example 5: Calculate Attendance Rate

SELECT employee_id, ROUND(100.0 * SUM(CASE WHEN attendance_status = 'Present' THEN 1 ELSE 0 END) / COUNT(*), 2) AS attendance_rate
FROM attendance
GROUP BY employee_id;

💡 Example 6: Rank Employees by Salary

SELECT employee_name, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

💡 Example 7: Find Employees Who Were Promoted

SELECT e.employee_name, p.old_job_title, p.new_job_title, p.promotion_date
FROM employees e
JOIN promotions p ON e.employee_id = p.employee_id
ORDER BY p.promotion_date;

🎯 Key Insights You Can Derive

🔹 Which departments have the highest headcount?

🔹 Which departments have the highest attrition?

🔹 Which roles have the highest salaries?

🔹 Which employees have been promoted?

🔹 What is the average employee tenure?

🔹 Which departments have attendance problems?

🔹 How quickly is the organization growing?

🔹 Where are the biggest employee retention challenges?

💼 Double Tap ❤️ For More

🚀 SQL Project Series #35

HR & Employee Analytics 👥

Analyze employees, departments, salaries, attendance, promotions, and attrition using SQL to understand workforce trends and improve HR decision-making.

🎯 Business Objectives

✅ Analyze employee headcount

✅ Track employee attrition

✅ Analyze salary distribution

✅ Measure department performance

✅ Identify high-performing employees

✅ Analyze promotions and tenure

✅ Track attendance

✅ Build HR analytics dashboards

📂 Step 1: Create Database

CREATE DATABASE hr_analytics_db;
USE hr_analytics_db;

📂 Step 2: Create Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(50)
);

📂 Step 3: Create Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    job_title VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(12,2),
    employment_status VARCHAR(20),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

📂 Step 4: Create Attendance Table

CREATE TABLE attendance (
    attendance_id INT PRIMARY KEY,
    employee_id INT,
    attendance_date DATE,
    attendance_status VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

📂 Step 5: Create Promotions Table

CREATE TABLE promotions (
    promotion_id INT PRIMARY KEY,
    employee_id INT,
    promotion_date DATE,
    old_job_title VARCHAR(100),
    new_job_title VARCHAR(100),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

📂 Step 6: Insert Sample Departments

INSERT INTO departments VALUES
(101,'Data Analytics','Mumbai'),
(102,'Finance','Delhi'),
(103,'Human Resources','Bangalore'),
(104,'Technology','Pune'),
(105,'Operations','Hyderabad');

📂 Step 7: Insert Sample Employees

INSERT INTO employees VALUES
(1,'Rahul Sharma',101,'Data Analyst','2022-01-10',850000,'Active'),
(2,'Priya Verma',102,'Financial Analyst','2021-05-15',920000,'Active'),
(3,'Amit Patel',104,'Software Engineer','2020-08-20',1200000,'Active'),
(4,'Sneha Joshi',103,'HR Specialist','2023-02-10',650000,'Active'),
(5,'Rohan Gupta',105,'Operations Analyst','2022-11-05',750000,'Resigned');

📂 Step 8: Insert Sample Attendance

INSERT INTO attendance VALUES
(1,1,'2025-01-02','Present'),
(2,1,'2025-01-03','Present'),
(3,2,'2025-01-02','Present'),
(4,2,'2025-01-03','Absent'),
(5,3,'2025-01-02','Present'),
(6,4,'2025-01-02','Present'),
(7,5,'2025-01-02','Absent');

📂 Step 9: Insert Sample Promotions

INSERT INTO promotions VALUES
(1,1,'2024-06-01','Junior Data Analyst','Data Analyst'),
(2,3,'2024-01-15','Software Engineer','Senior Software Engineer');

🧠 SQL Concepts You'll Practice

✔ INNER JOIN

✔ LEFT JOIN

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ Aggregate Functions

✔ CTEs

✔ Subqueries

✔ Window Functions

✔ RANK()

✔ DENSE_RANK()

✔ LAG()

✔ Date Functions

✔ Conditional Aggregation

📊 Business KPIs You Can Build

📈 Total Employees

📈 Active Employees

📈 Employee Attrition Rate

📈 Monthly Hiring Rate

📈 Monthly Attrition Rate

📈 Employee Retention Rate

📈 Average Salary

📈 Median Salary

📈 Salary by Department

📈 Salary by Job Title

📈 Gender Distribution

📈 Employee Tenure

📈 Average Tenure

📈 Promotion Rate

📈 Employees Promoted

📈 Attendance Rate

📈 Absenteeism Rate

📈 Department Headcount

📈 Department Attrition Rate

📈 Highest Paid Employees

📈 Salary Distribution

📈 Hiring Trend

📈 Employee Growth

📈 Executive HR Dashboard

💡 Example 1: Department-wise Employee Count

📊 𝗕𝘂𝗶𝗹𝗱 𝗬𝗼𝘂𝗿 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘀𝘁 𝗣𝗼𝗿𝘁𝗳𝗼𝗹𝗶𝗼 | 𝟱 𝗛𝗮𝗻𝗱𝘀-𝗢𝗻 𝗣𝗿𝗼𝗷𝗲𝗰𝘁𝘀 🚀

Learning Data Analytics? Don't stop with tutorials — build real projects that you can showcase on your resume and portfolio! 💻

🔥 Practice with 5 Hands-On Projects covering:

🗄️ SQL
📊 Excel
📈 Tableau
📉 Power BI

🔗𝗟𝗶𝗻𝗸 👇:- 

https://pdlink.in/45LLDH7

🎓 Perfect for Students | Freshers | Data Analyst Aspirants | Beginners

📊 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗙𝗥𝗘𝗘 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 🚀

Want to start a career in Data Analytics & Business Intelligence? Learn Power BI through Microsoft learning modules and build practical, job-relevant analytics skills.

🎯 Perfect for Students | Freshers | Data Analyst Aspirants | Working Professionals

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4zhGTX6

🔥 Start learning Power BI and turn raw data into powerful business insights!

𝗔𝗜 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝗶𝗻𝗴 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 😍

Build real AI products - not just prompts

🎯 Program Highlights:-

🚀 15+ AI Projects
👨‍🏫 Live Online Classes + 1-on-1 Mentorship
💼 End-to-End Placement Support
🤝 500+ Partner Companies
🎓 2000+ Students Placed
💰 Average Salary: ₹7.4 LPA
🏆 Highest Salary: ₹41 LPA

🔗 𝗕𝗼𝗼𝗸 𝗮 𝗙𝗥𝗘𝗘 𝗗𝗲𝗺𝗼 𝗖𝗹𝗮𝘀𝘀:-

https://pdlink.in/4fWJVID

🔥 Learn AI → Build Real Projects → Create Your Portfolio → Become Job Ready

🎯 Key Insights You Can Derive

🔹 Which products generate the most revenue?

🔹 Which stores have the highest sales?

🔹 When do customers place the most orders?

🔹 Which cities have the strongest demand?

🔹 What percentage of orders are cancelled?

🔹 Which customers make repeat purchases?

🔹 Which categories contribute most to revenue?

🔹 How efficiently are orders being delivered?

Double Tap ❤️ For More

🚀 SQL Project Series #34

Food & Grocery Delivery Analytics 🛒

Analyze customers, stores, products, orders, deliveries, and payments to understand sales performance, customer behavior, delivery efficiency, and operational costs.

🎯 Business Objectives

✅ Analyze order and revenue trends

✅ Identify top-selling products

✅ Measure customer retention

✅ Analyze store performance

✅ Track delivery efficiency

✅ Identify peak ordering periods

✅ Monitor cancellations and refunds

✅ Optimize product and store performance

📂 Database Setup

CREATE DATABASE grocery_delivery_db;
USE grocery_delivery_db;

Tables Created:

customers → customer_id, customer_name, city, signup_date

stores → store_id, store_name, city, store_type

products → product_id, product_name, category, price

orders → order_id, customer_id, store_id, order_date, order_status, delivery_time_minutes, delivery_fee

order_items → order_item_id, order_id, product_id, quantity, unit_price

Sample data for 5 customers, 4 stores, 5 products, 5 orders already included.

🧠 SQL Concepts You'll Practice

✔ INNER JOIN, LEFT JOIN

✔ Aggregate Functions, GROUP BY, HAVING

✔ CASE WHEN, CTEs, Subqueries

✔ Window Functions

✔ Date & Time Functions

✔ Conditional Aggregation

📊 Business KPIs You Can Build

📈 Total Orders, Completed Orders, Cancelled Orders, Cancellation Rate

📈 Total Revenue, AOV, Average Basket Size, Items Sold

📈 Revenue by Category, Store, City

📈 Top-Selling / Low-Selling Products

📈 Customer Lifetime Value, Repeat Purchase Rate, Retention Rate

📈 Average Delivery Time, On-Time Delivery Rate

📈 Peak Ordering Hour, Peak Ordering Day, Monthly Revenue Growth

📈 Delivery Fee Revenue, Customer Acquisition Trend

📈 Executive Grocery Delivery Dashboard

💡 Example Queries

1. Total Revenue

SELECT SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'Delivered';

2. Top-Selling Products

SELECT p.product_name, SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'Delivered'
GROUP BY p.product_name
ORDER BY units_sold DESC
LIMIT 10;

3. Average Order Value

WITH order_values AS (
    SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'Delivered'
    GROUP BY o.order_id
)
SELECT ROUND(AVG(order_value), 2) AS average_order_value FROM order_values;

4. Repeat Customers

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
WHERE order_status = 'Delivered'
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

5. Revenue by Store

SELECT s.store_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM stores s
JOIN orders o ON s.store_id = o.store_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'Delivered'
GROUP BY s.store_name
ORDER BY revenue DESC;

6. Cancellation Rate

SELECT ROUND(100.0 * SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate
FROM orders;

7. Peak Ordering Hours

SELECT EXTRACT(HOUR FROM order_date) AS order_hour, COUNT(*) AS total_orders
FROM orders
WHERE order_status = 'Delivered'
GROUP BY EXTRACT(HOUR FROM order_date)
ORDER BY total_orders DESC;

🇮🇳 𝗙𝗥𝗘𝗘 𝗚𝗼𝘃𝗲𝗿𝗻𝗺𝗲𝗻𝘁-𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗲𝗱 𝗢𝗻𝗹𝗶𝗻𝗲 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🎓

Upgrade your skills with *SWAYAM*, an initiative by the Government of India!

✅ Learn from leading institutes and expert educators
✅ Courses in AI, Programming, Data Science, Business & more
✅ Suitable for students, freshers and professionals
✅ Learn online at your own pace
✅ Strengthen your résumé with valuable certifications

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4gc1MKx

📢 Share this opportunity with your friends and classmates!

🚀 DATA ANALYTICS + AI: YOUR NEXT CAREER MOVE!

Data is everywhere. The right skills can put you ahead.
Join the PW Skills Data Analytics With AI Course and learn Excel, SQL, Python, Power BI & AI tools through live sessions and real-world projects.

✨ What you get:
✅ Industry-relevant Data Analytics skills
✅ AI-powered learning
✅ Microsoft collaboration
✅ Hands-on projects
✅ Job assistance*
✅ Live classes in Hinglish

📅 Starts: 14th August 2026
⏳ Duration: 5 Months
🔥 Ready to become a future-ready Data Analyst?
👉
Enroll Now & Start Your Upskilling Journey!
https://lp.pwskills.com/data-analytics-with-gen-ai-online-course?utm_source=telegram&utm_medium=influencer&utm_campaign=deepakDAonline

WITH ranked_videos AS (
    SELECT
        video_title,
        category,
        views,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY views DESC
        ) AS rn
    FROM videos
)
SELECT
    video_title,
    category,
    views
FROM ranked_videos
WHERE rn = 1;

💡 Example 4: Calculate Channel Performance 

SELECT
    c.channel_name,
    COUNT(v.video_id) AS total_videos,
    SUM(v.views) AS total_views,
    SUM(v.likes) AS total_likes,
    SUM(v.comments) AS total_comments
FROM channels c
LEFT JOIN videos v
    ON c.channel_id = v.channel_id
GROUP BY c.channel_name
ORDER BY total_views DESC;

💡 Example 5: Analyze Publishing Performance 

SELECT
    EXTRACT(DOW FROM publish_date) AS day_of_week,
    COUNT(*) AS total_videos,
    ROUND(AVG(views), 0) AS avg_views
FROM videos
GROUP BY EXTRACT(DOW FROM publish_date)
ORDER BY avg_views DESC;

💡 Example 6: Rank Videos by Views 

SELECT
    video_title,
    views,
    DENSE_RANK() OVER (
        ORDER BY views DESC
    ) AS view_rank
FROM videos;

🎯 Key Insights You Can Derive

🔹 Which videos generate the most views? 
🔹 Which content categories perform best? 
🔹 Which videos have high engagement but relatively low views? 
🔹 What publishing days generate the most views? 
🔹 Which channels have the strongest audience engagement? 
🔹 Which content contributes most to subscriber growth? 
🔹 Which videos should be promoted further? 

💼 This project is especially useful for Data Analysts, Product Analysts, Growth Analysts, Marketing Analysts, and Business Intelligence professionals working with content, media, and digital platforms.

Double Tap ❤️ For More

🚀 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

🚀 𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝟮𝟬𝟮𝟲 🎓

Want to upgrade your resume with Google skills and certifications Explore FREE learning opportunities and build in-demand skills for today's job market.

👉Artificial Intelligence & Generative AI
📊 Data Analytics
☁️ Cloud Computing
📢 Digital Marketing
🔐 Cybersecurity
💻 Tech & Career Skills

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4z9pdgf

🔥 Don't just collect certificates — build skills that can help you stand out in 2026!

🧠 SQL Concepts You'll Practice

✔ Joins

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ CTEs

✔ Subqueries

✔ Window Functions

✔ LAG()

✔ LEAD()

✔ Date Functions

✔ Conditional Aggregation

📊 Business KPIs You Can Build

📈 Monthly Recurring Revenue (MRR)

📈 Annual Recurring Revenue (ARR)

📈 Average Revenue Per User (ARPU)

📈 Customer Lifetime Value (CLV)

📈 Monthly Customer Churn Rate

📈 Revenue Churn Rate

📈 Customer Retention Rate

📈 New Customers, New MRR

📈 Expansion MRR, Contraction MRR, Churned MRR

📈 Net Revenue Retention (NRR), Gross Revenue Retention (GRR)

📈 Upgrade Rate, Downgrade Rate

📈 Plan-wise Revenue, Active Subscriptions, Cancelled Subscriptions

📈 Payment Success Rate, Failed Payment Rate

📈 Monthly Revenue Growth, Revenue Contribution by Plan

📈 Customer Segmentation

💡 Example 1: Calculate MRR

SELECT
    SUM(p.monthly_price) AS mrr
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.subscription_status = 'Active';

💡 Example 2: Revenue by Plan

SELECT
    p.plan_name,
    COUNT(s.subscription_id) AS active_subscriptions,
    SUM(p.monthly_price) AS monthly_revenue
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.subscription_status = 'Active'
GROUP BY p.plan_name
ORDER BY monthly_revenue DESC;

💡 Example 3: Identify Churned Customers

SELECT
    customer_id,
    subscription_id,
    end_date
FROM subscriptions
WHERE subscription_status = 'Cancelled';

💡 Example 4: Calculate Upgrade vs Downgrade Count

SELECT
    change_type,
    COUNT(*) AS total_changes
FROM subscription_changes
GROUP BY change_type;

💡 Example 5: Calculate Average Revenue Per Customer

SELECT
    ROUND(SUM(p.monthly_price) / COUNT(DISTINCT s.customer_id), 2) AS arpu
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.subscription_status = 'Active';

💡 Example 6: Rank Customers by MRR

WITH customer_mrr AS (
    SELECT
        s.customer_id,
        SUM(p.monthly_price) AS mrr
    FROM subscriptions s
    JOIN plans p ON s.plan_id = p.plan_id
    WHERE s.subscription_status = 'Active'
    GROUP BY s.customer_id
)
SELECT
    customer_id,
    mrr,
    DENSE_RANK() OVER (ORDER BY mrr DESC) AS revenue_rank
FROM customer_mrr;

🎯 Key Insights You Can Derive

🔹 Which subscription plan generates the most revenue?

🔹 Which plan has the highest churn?

🔹 How much MRR comes from new customers?

🔹 How much revenue is lost through cancellations?

🔹 Which customers have upgraded or downgraded?

🔹 Is revenue growing month over month?

🔹 Which customers contribute the most recurring revenue?

💼 This project is especially useful for Data Analysts, Product Analysts, Revenue Analysts, Growth Analysts, and Business Intelligence professionals working with SaaS and subscription-based businesses.

Double Tap ❤️ For More

🚀 SQL Project Series #32

SaaS Subscription & Revenue Analytics 💻💰

Analyze subscriptions, plans, payments, upgrades, downgrades, and churn to understand how a SaaS business grows revenue and retains customers.

🎯 Business Objectives

✅ Analyze subscription growth

✅ Calculate MRR and ARR

✅ Track upgrades and downgrades

✅ Measure customer churn

✅ Analyze revenue by plan

✅ Calculate ARPU and CLV

✅ Identify high-value customers

✅ Measure monthly revenue growth

📂 Step 1: Create Database

CREATE DATABASE saas_revenue_db;
USE saas_revenue_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    company_name VARCHAR(100),
    signup_date DATE,
    country VARCHAR(50)
);

📂 Step 3: Create Plans Table

CREATE TABLE plans (
    plan_id INT PRIMARY KEY,
    plan_name VARCHAR(50),
    monthly_price DECIMAL(10,2)
);

📂 Step 4: Create Subscriptions Table

CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    customer_id INT,
    plan_id INT,
    start_date DATE,
    end_date DATE,
    subscription_status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (plan_id) REFERENCES plans(plan_id)
);

📂 Step 5: Create Subscription Changes Table

CREATE TABLE subscription_changes (
    change_id INT PRIMARY KEY,
    subscription_id INT,
    change_date DATE,
    old_plan_id INT,
    new_plan_id INT,
    change_type VARCHAR(20),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id),
    FOREIGN KEY (old_plan_id) REFERENCES plans(plan_id),
    FOREIGN KEY (new_plan_id) REFERENCES plans(plan_id)
);

📂 Step 6: Create Payments Table

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    subscription_id INT,
    payment_date DATE,
    amount DECIMAL(10,2),
    payment_status VARCHAR(20),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id)
);

📂 Step 7: Insert Sample Customers

INSERT INTO customers VALUES
(1,'Rahul Sharma','DataPro','2025-01-05','India'),
(2,'Priya Verma','TechLabs','2025-01-10','India'),
(3,'Amit Patel','FinTech Solutions','2025-01-15','India'),
(4,'Sneha Joshi','CloudWorks','2025-02-01','India'),
(5,'Rohan Gupta','Analytics Hub','2025-02-10','India');

📂 Step 8: Insert Sample Plans

INSERT INTO plans VALUES
(101,'Basic',499),
(102,'Professional',999),
(103,'Business',2499),
(104,'Enterprise',4999);

📂 Step 9: Insert Sample Subscriptions

INSERT INTO subscriptions VALUES
(1001,1,102,'2025-01-05',NULL,'Active'),
(1002,2,101,'2025-01-10','2025-04-10','Cancelled'),
(1003,3,104,'2025-01-15',NULL,'Active'),
(1004,4,103,'2025-02-01',NULL,'Active'),
(1005,5,102,'2025-02-10',NULL,'Active');

📂 Step 10: Insert Sample Subscription Changes

INSERT INTO subscription_changes VALUES
(1,1001,'2025-03-01',101,102,'Upgrade'),
(2,1004,'2025-04-01',103,104,'Upgrade'),
(3,1005,'2025-05-01',102,101,'Downgrade');

📂 Step 11: Insert Sample Payments

INSERT INTO payments VALUES
(501,1001,'2025-02-01',999,'Paid'),
(502,1002,'2025-02-01',499,'Paid'),
(503,1003,'2025-02-01',4999,'Paid'),
(504,1004,'2025-02-01',2499,'Paid'),
(505,1005,'2025-02-01',999,'Paid');

🚀 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗯𝘆 𝗧𝗼𝗽 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀🔥

Get FREE access to company-specific interview kits, previous questions, preparation strategies, and important resources! 👇

Google :- https://pdlink.in/4xtUyIG

Amazon :- https://pdlink.in/45Q0YWR

Microsoft :- https://pdlink.in/3Up1bha

Wipro :- https://pdlink.in/4fMo1rA

Infosys :- https://pdlink.in/3TRn8p0

📌 share it with friends preparing for placements

🎯 Key Insights You Can Derive

🔹 Which suppliers contribute the most to procurement spending?

🔹 Which suppliers frequently deliver late?

🔹 Which products have the highest price variance?

🔹 Which suppliers offer the most competitive prices?

🔹 Which products are purchased most frequently?

🔹 Where can procurement costs be reduced?

🔹 Which suppliers may represent supply-chain risk? 

This project is especially useful for Data Analysts, Procurement Analysts, Supply Chain Analysts, Operations Analysts, and Business Intelligence professionals.

Double Tap ❤️ For More

🚀 SQL Project Series #31: Supply Chain & Procurement Analytics 🚚

Analyze suppliers, purchase orders, deliveries, procurement costs, and supplier performance using SQL to identify cost-saving opportunities and improve supply chain efficiency.

🎯 Business Objectives

✅ Analyze purchase orders

✅ Track supplier performance

✅ Measure procurement spending

✅ Identify delayed deliveries

✅ Analyze purchase costs

✅ Monitor order fulfillment

✅ Evaluate supplier quality

✅ Identify cost-saving opportunities

📂 Step 1: Create Database

CREATE DATABASE procurement_db;
USE procurement_db;

📂 Step 2: Create Suppliers Table

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(100),
    city VARCHAR(50),
    supplier_category VARCHAR(50)
);

📂 Step 3: Create Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    standard_cost DECIMAL(10,2)
);

📂 Step 4: Create Purchase Orders Table

CREATE TABLE purchase_orders (
    po_id INT PRIMARY KEY,
    supplier_id INT,
    po_date DATE,
    expected_date DATE,
    actual_delivery_date DATE,
    po_status VARCHAR(30),
    FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id)
);

📂 Step 5: Create Purchase Order Items Table

CREATE TABLE purchase_order_items (
    po_item_id INT PRIMARY KEY,
    po_id INT,
    product_id INT,
    quantity INT,
    unit_cost DECIMAL(10,2),
    FOREIGN KEY (po_id)
        REFERENCES purchase_orders(po_id),
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);

📂 Step 6: Insert Sample Suppliers

INSERT INTO suppliers VALUES
(1,'ABC Suppliers','Mumbai','Electronics'),
(2,'Global Traders','Delhi','Office Supplies'),
(3,'Prime Distributors','Pune','Electronics'),
(4,'Reliable Wholesale','Bangalore','Furniture'),
(5,'Metro Supplies','Hyderabad','General');

📂 Step 7: Insert Sample Products

INSERT INTO products VALUES
(101,'Laptop','Electronics',55000),
(102,'Monitor','Electronics',15000),
(103,'Keyboard','Accessories',1200),
(104,'Office Chair','Furniture',6500),
(105,'Printer','Office Equipment',18000);

📂 Step 8: Insert Sample Purchase Orders

INSERT INTO purchase_orders VALUES
(1001,1,'2025-01-05','2025-01-10','2025-01-09','Delivered'),
(1002,2,'2025-01-07','2025-01-12','2025-01-15','Delivered'),
(1003,3,'2025-01-10','2025-01-18','2025-01-22','Delayed'),
(1004,4,'2025-01-12','2025-01-20','2025-01-19','Delivered'),
(1005,5,'2025-01-15','2025-01-25',NULL,'Pending');

📂 Step 9: Insert Sample Purchase Order Items

INSERT INTO purchase_order_items VALUES
(1,1001,101,20,54000),
(2,1001,102,30,14500),
(3,1002,103,100,1100),
(4,1003,101,15,53500),
(5,1003,105,10,17500),
(6,1004,104,40,6200),
(7,1005,105,20,17200);

🚀 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 📊🔥

Build in-demand Data Analytics skills with Microsoft and strengthen your resume with FREE learning opportunities.

✅ Beginner-Friendly
✅ Learn at Your Own Pace
✅ Build Job-Ready Data Skills
✅ Improve Your Resume & LinkedIn Profile
✅ Prepare for Data Analyst & BI Careers

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4hXL4Ru

🔥 Start learning today and take your first step toward a career in Data Analytics & Business Intelligence

𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 & 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 📊

Start learning with FREE courses from leading companies and build in-demand skills for 2026.

🔹 Data Analytics Essentials — Cisco
🔹 Introduction to Data Science — Cisco
🔹 Python for Data Science — IBM
🔹 Azure Data Fundamentals — Microsoft
🔹 Google Analytics — Google

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/45QpA1I

🔥 Start learning today and upgrade your resume with job-ready Data & Analytics skills!