🚀 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