SQL Programming Resources

🎯 Final Portfolio KPIs

📊 Customer Acquisition, Retention, Churn

📊 Cohort Retention, Repeat Purchase Rate, CLV

📊 Revenue Growth, AOV, Purchase Frequency

📊 RFM Segmentation, High-Value Customer Analysis

📊 Monthly Revenue, Revenue by Cohort, Customer Engagement 

This project is especially useful for Data Analysts, Product Analysts, Growth Analysts, CRM Analysts, and Business Intelligence professionals because it combines SQL fundamentals with advanced customer analytics.

Double Tap ❤️ For More

🚀 SQL Project Series #30: E-Commerce Customer Retention & Cohort Analysis 📊

Perfect project for Data, Product, and CRM Analysts. This one hits both SQL fundamentals + advanced customer analytics.

🎯 Business Objectives

✅ Analyze customer retention

✅ Build monthly customer cohorts

✅ Measure repeat purchases

✅ Calculate customer lifetime value

✅ Identify high-value customers

✅ Analyze customer churn

✅ Compare cohort performance

✅ Measure revenue retention

📂 Step 1: Create Database

CREATE DATABASE cohort_analysis_db;
USE cohort_analysis_db;

📂 Step 2: Create Customers Table

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

📂 Step 3: Create Orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10,2),
    order_status VARCHAR(20),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

📂 Step 4: Insert Sample Customers

INSERT INTO customers VALUES
(1,'Rahul Sharma','2025-01-05','Mumbai'),
(2,'Priya Verma','2025-01-10','Delhi'),
(3,'Amit Patel','2025-02-15','Pune'),
(4,'Sneha Joshi','2025-02-20','Bangalore'),
(5,'Rohan Gupta','2025-03-05','Hyderabad'),
(6,'Anjali Shah','2025-03-15','Mumbai');

📂 Step 5: Insert Sample Orders

INSERT INTO orders VALUES
(1001,1,'2025-01-10',2500,'Completed'),
(1002,1,'2025-02-15',3200,'Completed'),
(1003,1,'2025-03-20',1800,'Completed'),
(1004,2,'2025-01-20',1500,'Completed'),
(1005,2,'2025-03-05',2800,'Completed'),
(1006,3,'2025-02-20',4200,'Completed'),
(1007,3,'2025-03-25',3500,'Completed'),
(1008,4,'2025-02-25',1800,'Completed'),
(1009,5,'2025-03-10',2200,'Completed'),
(1010,5,'2025-04-15',3100,'Completed'),
(1011,6,'2025-03-20',2700,'Completed');

🧠 SQL Concepts You'll Practice

✔ Date Functions

✔ GROUP BY, Aggregate Functions, JOINs

✔ CTEs, CASE WHEN, Subqueries

✔ Window Functions, LAG(), DENSE_RANK()

✔ COUNT DISTINCT, Cohort Analysis

📊 Business KPIs You Can Build

📈 Monthly Active Customers

📈 New Customers by Month

📈 Repeat Customers, Repeat Purchase Rate

📈 Customer Retention Rate, Churn Rate

📈 Customer Lifetime Value (CLV), AOV

📈 Revenue per Cohort, Cohort Retention Rate

📈 RFM Segmentation, High-Value Customer %

💡 Example Queries

1. Find Customer Cohort

WITH customer_cohort AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(signup_date)) AS cohort_month
    FROM customers
    GROUP BY customer_id
)
SELECT * FROM customer_cohort ORDER BY cohort_month;

2. Monthly Customer Activity

SELECT
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(DISTINCT customer_id) AS active_customers
FROM orders
WHERE order_status = 'Completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

3. Repeat Customers

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

4. Customer Lifetime Value

SELECT
    customer_id,
    SUM(order_amount) AS lifetime_value
FROM orders
WHERE order_status = 'Completed'
GROUP BY customer_id
ORDER BY lifetime_value DESC;

5. Rank Customers by Revenue

WITH customer_revenue AS (
    SELECT customer_id, SUM(order_amount) AS revenue
    FROM orders WHERE order_status = 'Completed' GROUP BY customer_id
)
SELECT
    customer_id,
    revenue,
    DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_revenue;

🚀 𝗧𝗼𝗽 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 𝗔𝘀𝗸𝗲𝗱 𝗯𝘆 𝗟𝗲𝗮𝗱𝗶𝗻𝗴 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀 📊

💼 Companies hiring Power BI professionals include: Microsoft, Deloitte, Accenture, Capgemini, TCS, Infosys, Cognizant, EY, PwC, KPMG, IBM, Wipro, and many more.

✅ Frequently Asked Interview Questions
✅ Beginner to Advanced Level Coverage
✅ Improve Your Problem-Solving Skills
✅ Build Interview Confidence
✅ Prepare for Top MNC Hiring Drives

𝐋𝐢𝐧𝐤👇:-

https://pdlink.in/4xqxg6v

🔥 Master Power BI interview concepts and take one step closer to landing your dream Data Analytics job!

🚀 SQL Project Series #29

Customer Support & Helpdesk Analytics 🎧
Analyze customers, support tickets, agents, resolutions, and customer satisfaction using SQL to improve support operations, reduce resolution time, and enhance customer experience.

🎯 Business Objectives
✅ Analyze support ticket volume
✅ Monitor agent performance
✅ Measure response and resolution time
✅ Identify recurring customer issues
✅ Analyze customer satisfaction
✅ Track SLA compliance
✅ Improve first-contact resolution
✅ Build executive support dashboards

📂 Step 1: Create Database
CREATE DATABASE helpdesk_db;
USE helpdesk_db;

📂 Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50),
signup_date DATE
);

📂 Step 3: Create Support Agents Table
CREATE TABLE support_agents (
agent_id INT PRIMARY KEY,
agent_name VARCHAR(100),
team_name VARCHAR(50),
joining_date DATE
);

📂 Step 4: Create Tickets Table
CREATE TABLE tickets (
ticket_id INT PRIMARY KEY,
customer_id INT,
agent_id INT,
issue_category VARCHAR(50),
priority VARCHAR(20),
created_at DATETIME,
resolved_at DATETIME,
ticket_status VARCHAR(20),
satisfaction_rating DECIMAL(2,1),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (agent_id) REFERENCES support_agents(agent_id)
);

📂 Step 5: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul Sharma','Mumbai','2025-01-05'),
(2,'Priya Verma','Delhi','2025-01-08'),
(3,'Amit Patel','Pune','2025-01-10'),
(4,'Sneha Joshi','Bangalore','2025-01-12'),
(5,'Rohan Gupta','Hyderabad','2025-01-15');

📂 Step 6: Insert Sample Support Agents
INSERT INTO support_agents VALUES
(101,'Ankit Mehta','Technical Support','2024-01-10'),
(102,'Neha Singh','Billing Support','2024-03-15'),
(103,'Vikas Sharma','Customer Success','2024-05-20'),
(104,'Pooja Verma','Technical Support','2024-07-01');

📂 Step 7: Insert Sample Tickets
INSERT INTO tickets VALUES
(1001,1,101,'Login Issue','High','2025-02-01 10:00:00','2025-02-01 11:30:00','Resolved',4.8),
(1002,2,102,'Billing Query','Medium','2025-02-02 09:15:00','2025-02-02 10:00:00','Resolved',4.5),
(1003,3,101,'Password Reset','Low','2025-02-02 14:30:00','2025-02-02 14:45:00','Resolved',5.0),
(1004,4,103,'Feature Request','Low','2025-02-03 12:00:00',NULL,'Open',NULL),
(1005,5,104,'Application Crash','Critical','2025-02-03 16:45:00',NULL,'In Progress',NULL);

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date & Time Functions
✔ Common Table Expressions (CTEs)
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Total Support Tickets
📈 Open Tickets
📈 Resolved Tickets
📈 Pending Tickets
📈 Average Resolution Time
📈 First Response Time
📈 First Contact Resolution Rate
📈 SLA Compliance Rate
📈 Customer Satisfaction Score (CSAT)
📈 Tickets by Priority
📈 Tickets by Category
📈 Tickets by City
📈 Agent Productivity
📈 Tickets Resolved per Agent
📈 Average Rating per Agent
📈 Daily Ticket Volume
📈 Monthly Ticket Trend
📈 Repeat Customer Issues
📈 Escalation Rate
📈 Executive Helpdesk Dashboard

🎯 This project reflects real-world SQL analysis performed by Customer Support Analysts, Operations Analysts, Service Delivery teams, Customer Success teams, and Business Intelligence professionals to improve service quality, optimize support operations, and enhance customer satisfaction.

Double Tap ❤️ For More

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

Upgrade your tech skills with 100% FREE IBM certification courses and build a strong foundation in AI, Data Science, Cloud Computing, SQL, Python, and Machine Learning.

🎯 Perfect For
🎓 Students & Freshers
👨‍💻 Software Developers
📊 Data Analysts
🤖 AI & Data Science Aspirants
💼 Working Professionals

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

https://pdlink.in/45KgqDR

🔥 Start learning today and prepare yourself for high-paying opportunities in the tech industry!

🚀 𝗙𝗥𝗘𝗘 𝗙𝗿𝗲𝘀𝗵𝗲𝗿 𝗛𝗶𝗿𝗶𝗻𝗴 𝗗𝗿𝗶𝘃𝗲 | 𝗧𝗲𝗰𝗵 𝗥𝗼𝗹𝗲𝘀 𝗨𝗽 𝘁𝗼 ₹𝟭𝟮 𝗟𝗣𝗔!🔥

Internship + Pre-Placement Offer

💼 Company: GoComet
💰 Stipend: ₹30,000–35,000/Month
🚀 PPO: Up to ₹12 LPA

📍 Assessment Centres: Pune | Hyderabad | Noida | Chennai | Bangalore

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:

Full Stack Intern:- https://pdlink.in/4z3vF8o

AI First SDET Interns :- https://pdlink.in/4hS1Am2

⏳ Limited Hiring Slots Available

🚀 SQL Project Series #28

Library Management Analytics 📚

Analyze books, members, authors, borrowings, returns, and fines using SQL to improve library operations, monitor book circulation, and enhance member engagement.

🎯 Business Objectives
✅ Track book borrowings
✅ Monitor overdue books
✅ Analyze member activity
✅ Measure book popularity
✅ Track fine collections
✅ Analyze author performance
✅ Improve inventory utilization
✅ Build executive library dashboards

📂 Step 1: Create Database
CREATE DATABASE library_db;
USE library_db;

📂 Step 2: Create Members Table
CREATE TABLE members (
member_id INT PRIMARY KEY,
member_name VARCHAR(100),
membership_type VARCHAR(30),
join_date DATE,
city VARCHAR(50)
);

📂 Step 3: Create Authors Table
CREATE TABLE authors (
author_id INT PRIMARY KEY,
author_name VARCHAR(100),
country VARCHAR(50)
);

📂 Step 4: Create Books Table
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(200),
author_id INT,
genre VARCHAR(50),
publication_year INT,
available_copies INT,
FOREIGN KEY (author_id)
REFERENCES authors(author_id)
);

📂 Step 5: Create Borrowings Table
CREATE TABLE borrowings (
borrowing_id INT PRIMARY KEY,
member_id INT,
book_id INT,
borrow_date DATE,
due_date DATE,
return_date DATE,
fine_amount DECIMAL(10,2),
FOREIGN KEY (member_id)
REFERENCES members(member_id),
FOREIGN KEY (book_id)
REFERENCES books(book_id)
);

📂 Step 6: Insert Sample Members
INSERT INTO members VALUES
(1,'Rahul Sharma','Premium','2024-01-10','Mumbai'),
(2,'Priya Verma','Standard','2024-02-15','Delhi'),
(3,'Amit Patel','Premium','2024-03-08','Pune'),
(4,'Sneha Joshi','Student','2024-04-20','Bangalore'),
(5,'Rohan Gupta','Standard','2024-05-12','Hyderabad');

📂 Step 7: Insert Sample Authors
INSERT INTO authors VALUES
(101,'James Clear','USA'),
(102,'Morgan Housel','USA'),
(103,'Yuval Noah Harari','Israel'),
(104,'Robert C. Martin','USA');

📂 Step 8: Insert Sample Books
INSERT INTO books VALUES
(201,'Atomic Habits',101,'Self Help',2018,12),
(202,'The Psychology of Money',102,'Finance',2020,8),
(203,'Sapiens',103,'History',2011,10),
(204,'Clean Code',104,'Programming',2008,6);

📂 Step 9: Insert Sample Borrowings
INSERT INTO borrowings VALUES
(1001,1,201,'2025-01-05','2025-01-19','2025-01-17',0),
(1002,2,202,'2025-01-08','2025-01-22','2025-01-25',150),
(1003,3,204,'2025-01-10','2025-01-24',NULL,0),
(1004,4,203,'2025-01-12','2025-01-26','2025-01-24',0),
(1005,5,201,'2025-01-15','2025-01-29',NULL,0);

▎🧠 SQL Concepts You'll Practice

✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

▎📊 Business KPIs You Can Build

📈 Total Books
📈 Total Members
📈 Active Members
📈 Total Borrowings
📈 Books Currently Issued
📈 Overdue Books
📈 Overdue Percentage
📈 Total Fine Collected
📈 Average Fine per Member
📈 Most Borrowed Books
📈 Least Borrowed Books
📈 Most Popular Authors
📈 Genre-wise Borrowings
📈 Monthly Borrowing Trend
📈 Book Availability Rate
📈 Average Borrowing Duration
📈 Repeat Borrowers
📈 Member Activity by City
📈 Library Utilization Rate
📈 Executive Library Dashboard

🎯 This project reflects real-world SQL analysis performed by Library Administrators, Educational Institutions, Public Libraries, Digital Library Platforms, and Business Intelligence professionals to optimize inventory, improve member engagement, and monitor library performance.

Double Tap ❤️ For More

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

Add these 100% FREE certification courses to your resume and gain valuable, job-ready skills that employers look for.

✅ 100% FREE Certification Courses
✅ Beginner-Friendly Learning
✅ Industry-Relevant Skills
✅ Self-Paced Online Learning
✅ Strengthen Your Resume & LinkedIn Profile
✅ Improve Your Job & Internship Opportunities

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

https://pdlink.in/4bwkOtA

🔥 Invest in your skills today and give your resume the competitive edge it deserves!

🚀 SQL Project Series #27

Stock Market Analytics 📈

Analyze stocks, investors, portfolios, trades, and market performance using SQL to gain insights into trading behavior, portfolio performance, and investment trends.

🎯 Business Objectives
✅ Analyze stock trading activity
✅ Track investor portfolios
✅ Monitor stock price movements
✅ Measure portfolio performance
✅ Identify top-performing stocks
✅ Analyze trading volume
✅ Calculate investment returns
✅ Build executive investment dashboards

📂 Step 1: Create Database
CREATE DATABASE stock_market_db;
USE stock_market_db;

📂 Step 2: Create Investors Table
CREATE TABLE investors (
investor_id INT PRIMARY KEY,
investor_name VARCHAR(100),
city VARCHAR(50),
account_open_date DATE
);

📂 Step 3: Create Stocks Table
CREATE TABLE stocks (
stock_id INT PRIMARY KEY,
stock_symbol VARCHAR(20),
company_name VARCHAR(100),
sector VARCHAR(50)
);

📂 Step 4: Create Trades Table
CREATE TABLE trades (
trade_id INT PRIMARY KEY,
investor_id INT,
stock_id INT,
trade_date DATE,
transaction_type VARCHAR(10),
quantity INT,
price DECIMAL(10,2),
FOREIGN KEY (investor_id) REFERENCES investors(investor_id),
FOREIGN KEY (stock_id) REFERENCES stocks(stock_id)
);

📂 Step 5: Create Daily Prices Table
CREATE TABLE daily_prices (
price_id INT PRIMARY KEY,
stock_id INT,
price_date DATE,
open_price DECIMAL(10,2),
close_price DECIMAL(10,2),
high_price DECIMAL(10,2),
low_price DECIMAL(10,2),
volume BIGINT,
FOREIGN KEY (stock_id) REFERENCES stocks(stock_id)
);

📂 Step 6: Insert Sample Investors
INSERT INTO investors VALUES
(1,'Rahul Sharma','Mumbai','2023-01-15'),
(2,'Priya Verma','Delhi','2023-04-20'),
(3,'Amit Patel','Pune','2024-02-10'),
(4,'Sneha Joshi','Bangalore','2024-05-05'),
(5,'Rohan Gupta','Hyderabad','2024-07-18');

📂 Step 7: Insert Sample Stocks
INSERT INTO stocks VALUES
(101,'TCS','Tata Consultancy Services','IT'),
(102,'INFY','Infosys','IT'),
(103,'RELIANCE','Reliance Industries','Energy'),
(104,'HDFCBANK','HDFC Bank','Banking'),
(105,'TATAMOTORS','Tata Motors','Automobile');

📂 Step 8: Insert Sample Trades
INSERT INTO trades VALUES
(1001,1,101,'2025-01-05','BUY',10,4200),
(1002,2,102,'2025-01-06','BUY',20,1800),
(1003,3,103,'2025-01-07','SELL',5,2900),
(1004,4,104,'2025-01-08','BUY',15,1650),
(1005,5,105,'2025-01-09','BUY',25,980);

📂 Step 9: Insert Sample Daily Prices
INSERT INTO daily_prices VALUES
(1,101,'2025-01-05',4180,4215,4235,4170,1200000),
(2,102,'2025-01-06',1785,1810,1822,1778,980000),
(3,103,'2025-01-07',2880,2910,2925,2870,2100000),
(4,104,'2025-01-08',1640,1665,1672,1635,1750000),
(5,105,'2025-01-09',970,990,995,965,3100000);

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Total Investors
📈 Total Trades
📈 Buy vs Sell Transactions
📈 Total Trading Volume
📈 Portfolio Value
📈 Profit & Loss (P&L)
📈 Return on Investment (ROI)
📈 Average Buy Price
📈 Average Sell Price
📈 Top Performing Stocks
📈 Worst Performing Stocks
📈 Daily Price Change
📈 Monthly Trading Volume
📈 Sector-wise Performance
📈 Most Traded Stocks
📈 Investor Portfolio Diversification
📈 Average Holding Size
📈 Stock Volatility
📈 Market Capitalization Trend
📈 Executive Investment Dashboard

🎯 Double Tap ❤️ For More

🚀 𝗠𝗮𝘀𝘁𝗲𝗿 𝗔𝗜 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 | 𝟱 𝗠𝘂𝘀𝘁-𝗧𝗮𝗸𝗲 𝗚𝗼𝗼𝗴𝗹𝗲 𝗔𝗜 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🔥

Artificial Intelligence is transforming every industry—and now you can learn directly from Google with 100% FREE AI courses!

🎯 Perfect For
🎓 Students & Freshers
👨‍💻 Software Developers
📊 Data Analysts
💫 AI & Machine Learning Aspirants
💼 Working Professionals

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

https://pdlink.in/45HWa5Q

🔥 Start your AI journey today and stay ahead in the era of Artificial Intelligence!

🚀 SQL Project Series #26

SaaS Product Analytics 💻

Analyze users, subscriptions, feature usage, payments, and customer behavior using SQL to improve product adoption, retention, and revenue.

🎯 Business Objectives
✅ Analyze user growth
✅ Track subscription plans
✅ Monitor feature adoption
✅ Measure Monthly Recurring Revenue (MRR)
✅ Analyze customer retention
✅ Identify customer churn
✅ Measure product engagement
✅ Build executive SaaS dashboards

📂 Step 1: Create Database
CREATE DATABASE saas_analytics_db;
USE saas_analytics_db;

📂 Step 2: Create Users Table
CREATE TABLE users (
user_id INT PRIMARY KEY,
user_name VARCHAR(100),
company_name VARCHAR(100),
country VARCHAR(50),
signup_date DATE
);

📂 Step 3: Create Subscription Plans Table
CREATE TABLE subscription_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,
user_id INT,
plan_id INT,
start_date DATE,
end_date DATE,
subscription_status VARCHAR(20),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (plan_id) REFERENCES subscription_plans(plan_id)
);

📂 Step 5: Create Feature Usage Table
CREATE TABLE feature_usage (
usage_id INT PRIMARY KEY,
user_id INT,
feature_name VARCHAR(100),
usage_date DATE,
usage_count INT,
FOREIGN KEY (user_id) REFERENCES users(user_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-11: Sample Data
Users, Subscription Plans, Subscriptions, Feature Usage, and Payments tables are populated with sample data for Rahul, Priya, John, Emma, and Amit across Free, Basic, Pro, and Enterprise plans.

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN / LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY / HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Growth: Total Users, Active Users, Paid Users, Free vs Paid Users, Subscription Growth
📈 Revenue: MRR, ARR, ARPU, CLV, Revenue by Subscription Plan, Payment Success Rate
📈 Retention: Customer Churn Rate, Customer Retention Rate, DAU, MAU, DAU/MAU Ratio
📈 Product: Feature Adoption Rate, Most Used Features, Feature Usage by Plan
📈 Customers: Top Enterprise Customers, Executive SaaS Dashboard

This project reflects real-world SQL analysis performed by Product Analysts, Growth Analysts, Customer Success teams, Revenue Operations teams, and BI professionals at SaaS companies like Salesforce, HubSpot, Atlassian, Notion, Slack, and Zoom.

Double Tap ❤️ For More

𝟯 𝗧𝗼𝗽 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝗕𝗼𝗼𝗸 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗻𝘀𝗲𝗹𝗹𝗶𝗻𝗴 𝗦𝗲𝘀𝘀𝗶𝗼𝗻 𝗜𝗻 𝗖𝗵𝗲𝗻𝗻𝗮𝗶😍
​
Learnfrom India's Best Mentors , Get 100% Placement Assistance

💫Data Analytics :- https://pdlink.in/4q59ef1
​
💫Fullstack :- https://pdlink.in/4he12a2
​
💫AI :- https://pdlink.in/4he5mpO
​
In Today's competitive world, you need industry-relevant skills taught by the best.

🚀 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗦𝗸𝗶𝗹𝗹𝘀 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘! 💻🔥

Want to future-proof your career without spending a single rupee? These 4 beginner-friendly FREE courses will help you build practical, job-ready skills

📚 FREE Courses Included
📊 Business Intelligence Using Excel
🤖 Generative AI for Beginners
💻 C Programming for Beginners
💫 Python Interview Questions & Answers

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

https://pdlink.in/4hSgTuW

🔥 Don't wait—start learning today and unlock better career opportunities!

🧠 SQL Concepts You'll Practice

✔ DDL & DML

✔ INNER JOIN

✔ LEFT JOIN

✔ Aggregate Functions

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ Date Functions

✔ CTEs

✔ Window Functions

✔ Ranking Functions

📊 Business KPIs You Can Build

📈 Total Units Produced

📈 Production Efficiency

📈 Machine Utilization Rate

📈 Production by Machine

📈 Production by Product

📈 Daily Production Trend

📈 Monthly Production Trend

📈 Defect Rate

📈 Quality Yield

📈 Total Defective Units

📈 Top Performing Machines

📈 Lowest Performing Machines

📈 Machine Downtime

📈 Maintenance Cost

📈 Maintenance Frequency

📈 Production Hours Analysis

📈 Cost per Unit Produced

📈 Production Line Performance

📈 Product-wise Manufacturing Cost

📈 Executive Manufacturing Dashboard

🎯 This project reflects real-world SQL analysis performed by Manufacturing Analysts, Operations Analysts, Production Engineers, Supply Chain teams, and Business Intelligence professionals to improve production efficiency, reduce defects, minimize downtime, and optimize manufacturing costs.

Double Tap ❤️ For More

🚀 SQL Project Series #25

Manufacturing Production Analytics 🏭

Analyze production, machines, inventory, quality, and maintenance using SQL to improve operational efficiency, reduce downtime, and optimize manufacturing performance.

🎯 Business Objectives

✅ Monitor production output

✅ Analyze machine utilization

✅ Track product quality

✅ Measure production efficiency

✅ Identify production bottlenecks

✅ Analyze inventory consumption

✅ Monitor machine maintenance

✅ Build executive manufacturing dashboards

📂 Step 1: Create Database

CREATE DATABASE manufacturing_db;
USE manufacturing_db;

📂 Step 2: Create Products Table

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

📂 Step 3: Create Machines Table

CREATE TABLE machines (
    machine_id INT PRIMARY KEY,
    machine_name VARCHAR(100),
    production_line VARCHAR(50),
    installation_date DATE
);

📂 Step 4: Create Production Table

CREATE TABLE production (
    production_id INT PRIMARY KEY,
    product_id INT,
    machine_id INT,
    production_date DATE,
    units_produced INT,
    defective_units INT,
    production_hours DECIMAL(5,2),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (machine_id) REFERENCES machines(machine_id)
);

📂 Step 5: Create Maintenance Table

CREATE TABLE maintenance (
    maintenance_id INT PRIMARY KEY,
    machine_id INT,
    maintenance_date DATE,
    maintenance_type VARCHAR(50),
    downtime_hours DECIMAL(5,2),
    maintenance_cost DECIMAL(10,2),
    FOREIGN KEY (machine_id) REFERENCES machines(machine_id)
);

📂 Step 6: Insert Sample Products

INSERT INTO products VALUES
(101,'Laptop','Electronics',45000),
(102,'Smartphone','Electronics',25000),
(103,'Tablet','Electronics',18000),
(104,'Smart Watch','Wearables',8000),
(105,'Wireless Earbuds','Accessories',3500);

📂 Step 7: Insert Sample Machines

INSERT INTO machines VALUES
(201,'Assembly Line A','Line 1','2022-01-15'),
(202,'Assembly Line B','Line 1','2022-06-20'),
(203,'Packaging Machine','Line 2','2023-02-10'),
(204,'Quality Inspection','Line 3','2023-08-18');

📂 Step 8: Insert Sample Production Data

INSERT INTO production VALUES
(1001,101,201,'2025-01-05',250,5,8.5),
(1002,102,202,'2025-01-05',420,8,9.0),
(1003,103,201,'2025-01-06',180,3,7.5),
(1004,104,203,'2025-01-06',520,10,8.0),
(1005,105,204,'2025-01-07',650,6,7.0);

📂 Step 9: Insert Sample Maintenance Data

INSERT INTO maintenance VALUES
(1,201,'2025-01-08','Preventive',2.5,12000),
(2,202,'2025-01-10','Corrective',5.0,25000),
(3,203,'2025-01-12','Preventive',1.5,9000),
(4,204,'2025-01-15','Inspection',1.0,5000);

🚀 SQL Project Series #24

Real Estate Property Analytics 🏠

Analyze properties, buyers, agents, sales, and rentals using SQL to understand market trends, optimize pricing, and improve business performance.

🎯 Business Objectives
✅ Analyze property sales
✅ Monitor rental performance
✅ Evaluate agent performance
✅ Track customer preferences
✅ Analyze property prices
✅ Measure market trends
✅ Identify high-demand locations
✅ Build executive dashboards

📂 Step 1: Create Database

CREATE DATABASE real_estate_db;
USE real_estate_db;

📂 Step 2: Create Agents Table

CREATE TABLE agents (
    agent_id INT PRIMARY KEY,
    agent_name VARCHAR(100),
    city VARCHAR(50),
    experience_years INT
);

📂 Step 3: Create Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50),
    customer_type VARCHAR(20)
);

📂 Step 4: Create Properties Table

CREATE TABLE properties (
    property_id INT PRIMARY KEY,
    property_type VARCHAR(50),
    city VARCHAR(50),
    bedrooms INT,
    listing_price DECIMAL(12,2),
    listing_date DATE,
    agent_id INT,
    FOREIGN KEY (agent_id)
        REFERENCES agents(agent_id)
);

📂 Step 5: Create Transactions Table

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    property_id INT,
    customer_id INT,
    transaction_type VARCHAR(20),
    transaction_date DATE,
    sale_price DECIMAL(12,2),
    FOREIGN KEY (property_id)
        REFERENCES properties(property_id),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

📂 Step 6: Insert Sample Agents

INSERT INTO agents VALUES
(101,'Amit Sharma','Mumbai',8),
(102,'Priya Singh','Delhi',6),
(103,'Rahul Mehta','Pune',10),
(104,'Sneha Patel','Bangalore',5);

📂 Step 7: Insert Sample Customers

INSERT INTO customers VALUES
(1,'Rahul Verma','Mumbai','Buyer'),
(2,'Anjali Gupta','Delhi','Tenant'),
(3,'Rohan Patel','Pune','Buyer'),
(4,'Neha Sharma','Bangalore','Tenant'),
(5,'Aakash Shah','Hyderabad','Buyer');

📂 Step 8: Insert Sample Properties

INSERT INTO properties VALUES
(1001,'Apartment','Mumbai',2,8500000,'2025-01-05',101),
(1002,'Villa','Delhi',4,22000000,'2025-01-08',102),
(1003,'Apartment','Pune',3,9500000,'2025-01-12',103),
(1004,'Studio','Bangalore',1,4200000,'2025-01-15',104),
(1005,'Villa','Hyderabad',5,28000000,'2025-01-18',103);

📂 Step 9: Insert Sample Transactions

INSERT INTO transactions VALUES
(5001,1001,1,'Sale','2025-02-01',8300000),
(5002,1002,2,'Rent','2025-02-03',45000),
(5003,1003,3,'Sale','2025-02-05',9200000),
(5004,1004,4,'Rent','2025-02-08',28000),
(5005,1005,5,'Sale','2025-02-12',27500000);

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Total Properties Listed
📈 Total Properties Sold
📈 Total Rental Properties
📈 Total Sales Revenue
📈 Average Property Price
📈 Average Rent
📈 Revenue by City
📈 Revenue by Property Type
📈 Top Performing Agents
📈 Agent-wise Sales
📈 Average Selling Time
📈 Property Listing Trend
📈 Monthly Sales Trend
📈 Most Expensive Property Sold
📈 Cheapest Property Sold
📈 Property Demand by City
📈 Buyer vs Tenant Ratio
📈 Average Bedrooms Sold
📈 Conversion Rate (Listed to Sold)
📈 Executive Real Estate Dashboard

🎯 This project reflects real-world SQL analysis performed by Real Estate Analysts, Property Management teams, Sales Analysts, and Business Intelligence professionals to optimize pricing, monitor sales performance, and identify market trends.

Double Tap ❤️ For More

📊 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽 𝗣𝗿𝗼𝗴𝗿𝗮𝗺 🚀

Company Name :- Collegedunia

✅ Role: Data Analyst Intern
📍 Location: Gurugram, Haryana
🏢 Work Mode: On-site
👩‍💻 Experience: Freshers / Students

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:

https://pdlink.in/3RNPbF7

⏳ Apply Before the link expires!

🚀 SQL Project Series #23

Movie Ticket Booking Analytics 🎬

Analyze movies, theatres, customers, bookings, and payments using SQL to improve occupancy, revenue, and customer experience.

🎯 Business Objectives
✅ Analyze ticket bookings
✅ Track movie performance
✅ Measure theatre occupancy
✅ Analyze customer behavior
✅ Monitor payment trends
✅ Identify peak show timings
✅ Optimize pricing strategy
✅ Build executive dashboards

📂 Step 1: Create Database
CREATE DATABASE movie_booking_db;
USE movie_booking_db;

📂 Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
gender VARCHAR(10),
city VARCHAR(50),
signup_date DATE
);

📂 Step 3: Create Movies Table
CREATE TABLE movies (
movie_id INT PRIMARY KEY,
movie_name VARCHAR(100),
genre VARCHAR(50),
language VARCHAR(30),
duration_minutes INT
);

📂 Step 4: Create Theatres Table
CREATE TABLE theatres (
theatre_id INT PRIMARY KEY,
theatre_name VARCHAR(100),
city VARCHAR(50),
total_seats INT
);

📂 Step 5: Create Bookings Table
CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
customer_id INT,
movie_id INT,
theatre_id INT,
show_date DATETIME,
seats_booked INT,
ticket_amount DECIMAL(10,2),
booking_status VARCHAR(20),
payment_method VARCHAR(30),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
FOREIGN KEY (theatre_id) REFERENCES theatres(theatre_id)
);

📂 Step 6: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul Sharma','Male','Mumbai','2025-01-10'),
(2,'Priya Verma','Female','Delhi','2025-01-15'),
(3,'Amit Patel','Male','Pune','2025-01-18'),
(4,'Sneha Joshi','Female','Bangalore','2025-01-20'),
(5,'Rohan Gupta','Male','Hyderabad','2025-01-22');

📂 Step 7: Insert Sample Movies
INSERT INTO movies VALUES
(101,'Leo','Action','Tamil',164),
(102,'Pushpa 2','Action','Telugu',180),
(103,'12th Fail','Drama','Hindi',147),
(104,'Inside Out 2','Animation','English',96);

📂 Step 8: Insert Sample Theatres
INSERT INTO theatres VALUES
(201,'PVR Phoenix','Mumbai',250),
(202,'INOX Select City','Delhi',220),
(203,'Cinepolis Seasons','Pune',180),
(204,'PVR Orion','Bangalore',240);

📂 Step 9: Insert Sample Bookings
INSERT INTO bookings VALUES
(1001,1,101,201,'2025-02-01 18:30:00',2,900,'Confirmed','UPI'),
(1002,2,102,202,'2025-02-01 20:00:00',3,1350,'Confirmed','Credit Card'),
(1003,3,103,203,'2025-02-02 16:00:00',1,350,'Cancelled','UPI'),
(1004,4,104,204,'2025-02-02 19:30:00',4,1800,'Confirmed','Debit Card'),
(1005,5,101,201,'2025-02-03 21:00:00',2,900,'Confirmed','Wallet');

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Total Bookings
📈 Total Tickets Sold
📈 Total Revenue
📈 Average Ticket Price
📈 Average Booking Value
📈 Booking Cancellation Rate
📈 Occupancy Rate
📈 Revenue by Movie
📈 Revenue by Theatre
📈 Revenue by City
📈 Most Popular Movie
📈 Most Popular Genre
📈 Peak Booking Hours
📈 Peak Show Timings
📈 Weekend vs Weekday Bookings
📈 Payment Method Distribution
📈 Customer Retention Rate
📈 Repeat Customers
📈 Top Spending Customers
📈 Theatre Utilization
📈 Language-wise Revenue
📈 Monthly Revenue Trend
📈 Movie Performance Dashboard
📈 Executive Booking Dashboard

🎯 This project reflects real-world SQL analysis performed by cinema chains, online ticketing platforms like BookMyShow, entertainment companies, and Business Intelligence teams to optimize occupancy, improve customer experience, and maximize revenue.

Double Tap ❤️ For More

𝐏𝐚𝐲 𝐀𝐟𝐭𝐞𝐫 𝐏𝐥𝐚𝐜𝐞𝐦𝐞𝐧𝐭 - 𝐆𝐞𝐭 𝐏𝐥𝐚𝐜𝐞𝐝 𝐈𝐧 𝐓𝐨𝐩 𝐌𝐍𝐂'𝐬 😍

Learn Coding From Scratch - Lectures Taught By IIT Alumni

💫Upskill on the most in-demand skills in the market

𝗛𝗶𝗴𝗵𝗹𝗶𝗴𝗵𝘁𝘀:-

💼 Avg. Package: ₹7.2 LPA | Highest: ₹41 LPA

🌟 Trusted by 7500+ Students
🤝 500+ Hiring Partners

Eligibility: BTech / BCA / BSc / MCA / MSc

𝐑𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐍𝐨𝐰 👇:-

 https://pdlink.in/42WOE5H

Hurry! Limited seats are available.🏃‍♂️

🚀 SQL Project Series #22

Digital Marketing Campaign Analytics 📢

Analyze marketing campaigns, customers, leads, conversions, and advertising performance using SQL to optimize ROI and improve campaign effectiveness.

🎯 Business Objectives
✅ Analyze marketing campaign performance
✅ Track leads and conversions
✅ Measure campaign ROI
✅ Optimize advertising spend
✅ Analyze customer acquisition
✅ Measure conversion funnel
✅ Evaluate marketing channels
✅ Build executive marketing dashboards

📂 Step 1: Create Database
CREATE DATABASE marketing_analytics_db;
USE marketing_analytics_db;

📂 Step 2: Create Campaigns Table
CREATE TABLE campaigns (
campaign_id INT PRIMARY KEY,
campaign_name VARCHAR(100),
marketing_channel VARCHAR(50),
campaign_start DATE,
campaign_end DATE,
budget DECIMAL(12,2)
);

📂 Step 3: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50),
acquisition_channel VARCHAR(50),
signup_date DATE
);

📂 Step 4: Create Leads Table
CREATE TABLE leads (
lead_id INT PRIMARY KEY,
campaign_id INT,
customer_id INT,
lead_date DATE,
lead_status VARCHAR(30),
conversion_date DATE,
FOREIGN KEY (campaign_id)
REFERENCES campaigns(campaign_id),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

📂 Step 5: Insert Sample Campaigns
INSERT INTO campaigns VALUES
(101,'Summer Sale','Google Ads','2025-01-01','2025-01-31',500000),
(102,'New Year Offer','Facebook Ads','2025-01-05','2025-01-25',350000),
(103,'Email Promotion','Email','2025-01-10','2025-01-30',100000),
(104,'Influencer Campaign','Instagram','2025-01-15','2025-02-05',450000);

📂 Step 6: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul Sharma','Mumbai','Google Ads','2025-01-08'),
(2,'Priya Verma','Delhi','Facebook Ads','2025-01-10'),
(3,'Amit Patel','Pune','Email','2025-01-12'),
(4,'Sneha Joshi','Bangalore','Instagram','2025-01-18'),
(5,'Rohan Gupta','Hyderabad','Google Ads','2025-01-22');

📂 Step 7: Insert Sample Leads
INSERT INTO leads VALUES
(1001,101,1,'2025-01-03','Converted','2025-01-08'),
(1002,102,2,'2025-01-06','Converted','2025-01-10'),
(1003,103,3,'2025-01-12','Open',NULL),
(1004,104,4,'2025-01-18','Converted','2025-01-21'),
(1005,101,5,'2025-01-22','Lost',NULL);

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ Joins
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ CTEs
✔ Window Functions
✔ Date Functions
✔ Marketing KPI Analysis

📊 Business KPIs You Can Build
📈 Total Campaigns
📈 Total Leads
📈 Total Conversions
📈 Conversion Rate
📈 Cost Per Lead (CPL)
📈 Cost Per Acquisition (CPA)
📈 Campaign ROI
📈 Campaign Spend vs Revenue
📈 Leads by Marketing Channel
📈 Conversions by Marketing Channel
📈 Customer Acquisition by Channel
📈 Monthly Lead Trend
📈 Monthly Conversion Trend
📈 Average Conversion Time
📈 Top Performing Campaigns
📈 Lowest Performing Campaigns
📈 Budget Utilization
📈 Revenue by Campaign
📈 Revenue by Marketing Channel
📈 Customer Acquisition Cost (CAC)
📈 Funnel Drop-off Analysis
📈 Channel-wise Performance Dashboard
📈 Campaign Performance Dashboard
📈 Marketing Executive Dashboard

🎯 This project reflects real-world SQL analysis performed by Marketing Analysts, Growth Analysts, Performance Marketing teams, Product Analysts, and Business Intelligence professionals to optimize campaign performance and maximize return on investment.

Double Tap ❤️ For More