Data Analytics
111K subscribers
212 photos
2 files
931 links
Perfect channel to learn Data Analytics

Learn SQL, Python, Alteryx, Tableau, Power BI and many more

For Promotions: @coderfun @love_data
Download Telegram
โ€ข This can help marketing teams identify potential customers who need activation campaigns.

๐Ÿ”น 9. Customers Above Average Spending

First calculate customer totals. Then compare them with the overall average.

WITH Customer_Sales AS (
SELECT Customer_ID, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Customer_ID
)
SELECT Customer_ID, Total_Sales
FROM Customer_Sales
WHERE Total_Sales > (SELECT AVG(Total_Sales) FROM Customer_Sales);


โ€ข Notice how concepts from earlier parts work together: CTE + Aggregation + Subquery

๐Ÿ”น 10. Month-over-Month Sales Growth

First aggregate sales by month. Then use LAG().

WITH Monthly_Sales AS (
SELECT EXTRACT(YEAR FROM Order_Date) AS Year,
EXTRACT(MONTH FROM Order_Date) AS Month,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY EXTRACT(YEAR FROM Order_Date), EXTRACT(MONTH FROM Order_Date)
),
Comparison AS (
SELECT Year, Month, Total_Sales,
LAG(Total_Sales) OVER (ORDER BY Year, Month) AS Previous_Sales
FROM Monthly_Sales
)
SELECT Year, Month, Total_Sales, Previous_Sales, Total_Sales - Previous_Sales AS Sales_Change
FROM Comparison;


โ€ข This produces a time-based comparison instead of just a total.

๐Ÿ”น 11. Calculate Growth Percentage

You can extend the previous query:

(Total_Sales - Previous_Sales) / NULLIF(Previous_Sales, 0) * 100


โ€ข NULLIF() is important because it prevents division-by-zero errors.

โ€ข A practical analyst must always think about edge cases.

๐Ÿ”น 12. Find the Latest Order for Every Customer

From Part 17, combine PARTITION BY + ORDER BY + ROW_NUMBER()

WITH Ranked_Orders AS (
SELECT Customer_ID, Order_ID, Order_Date,
ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date DESC) AS rn
FROM Orders
)
SELECT Customer_ID, Order_ID, Order_Date FROM Ranked_Orders WHERE rn = 1;


This is useful for:

โ€ข Customer activity

โ€ข Churn analysis

โ€ข Last purchase reporting

โ€ข CRM segmentation

๐Ÿ”น 13. Identify Potentially Inactive Customers

First find the last purchase date:

SELECT Customer_ID, MAX(Order_Date) AS Last_Order_Date FROM Orders GROUP BY Customer_ID;


Then compare the last order against a chosen inactivity threshold.

โ€ข The business rule might be: No purchase for 90 days โ†’ Potentially inactive

โ€ข The important lesson: SQL provides the calculation. The business defines what "inactive" means.

๐Ÿ”น 14. Find Duplicate Records

Window functions are excellent for detecting duplicates.

WITH Duplicate_Check AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Customer_ID, Order_Date, Sales ORDER BY Order_ID) AS rn
FROM Orders
)
SELECT * FROM Duplicate_Check WHERE rn > 1;


โ€ข These records can then be investigated before cleaning the dataset.

๐Ÿ”น 15. Combining Multiple Tables

Real analysis often requires several joins.

For example: Customers โ†’ Orders โ†’ Order_Items โ†’ Products

SELECT c.Customer_ID, c.Customer_Name, p.Product_Name, oi.Quantity, oi.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
JOIN Order_Items oi ON o.Order_ID = oi.Order_ID
JOIN Products p ON oi.Product_ID = p.Product_ID;
โค3
โš ๏ธ Every additional join can change the number of rows.

โ€ข Always check whether the resulting grain is still what you expect.

๐Ÿ”น 16. The Most Important Analytical Pattern

Many advanced SQL problems can be solved using this structure:

โ€ข

1. Filter the raw data โ†’ 2. Join required tables โ†’ 3. Aggregate to the correct grain โ†’ 4. Apply window functions โ†’ 5. Filter the analytical result โ†’ 6. Present the final output

For example:

โ€ข Orders โ†’ Filter โ†’ GROUP BY Customer โ†’ Calculate Sales โ†’ RANK() โ†’ Keep Top 3 โ†’ Final Report

โ€ข Learning this thought process is more valuable than memorizing individual queries.

๐Ÿ’ผ Real-World Business Problems You Should Practice

Sales

โœ… Top 5 products by revenue

โœ… Top products within each category

โœ… Month with highest sales

โœ… Revenue growth by month

โœ… Customers contributing most revenue

Customers

โœ… Customers with no orders

โœ… Customers with declining purchases

โœ… Most recent purchase per customer

โœ… Repeat customers

โœ… Average order value per customer

Operations

โœ… Orders taking longer than expected

โœ… Products never sold

โœ… Duplicate transactions

โœ… Most active regions

โœ… Employees with above-average performance

๐ŸŽฏ SQL Interview Challenge

Question: Find the highest-selling product in each category.

Think about the problem before writing the query:

โ€ข Product โ†’ Category โ†’ Total Sales โ†’ Rank Within Category โ†’ Keep Rank 1

A possible solution:

WITH Product_Sales AS (
SELECT Product_ID, Category, SUM(Sales) AS Total_Sales
FROM Product_Sales_Data
GROUP BY Product_ID, Category
),
Ranked_Products AS (
SELECT Product_ID, Category, Total_Sales,
RANK() OVER (PARTITION BY Category ORDER BY Total_Sales DESC) AS Sales_Rank
FROM Product_Sales
)
SELECT Product_ID, Category, Total_Sales FROM Ranked_Products WHERE Sales_Rank = 1;


๐Ÿ’ก Double Tap โค๏ธ For More
โค9
๐Ÿš€ ๐—™๐—ฅ๐—˜๐—˜ ๐—ฅ๐—ฒ๐˜€๐—ผ๐˜‚๐—ฟ๐—ฐ๐—ฒ๐˜€ ๐˜๐—ผ ๐—Ÿ๐—ฒ๐—ฎ๐—ฟ๐—ป ๐——๐—ฎ๐˜๐—ฎ ๐—”๐—ป๐—ฎ๐—น๐˜†๐˜๐—ถ๐—ฐ๐˜€ ๐Ÿ“Š

Want to build a career in Data Analytics but donโ€™t know where to start? Learn the most important skills completely FREE with these expert YouTube resources.

๐Ÿ”ฅ Learn โ†’ Practice โ†’ Build Projects โ†’ Become Job-Ready

๐Ÿ”— ๐—˜๐—ป๐—ฟ๐—ผ๐—น๐—น ๐—ณ๐—ผ๐—ฟ ๐—™๐—ฅ๐—˜๐—˜ ๐Ÿ‘‡:-

https://pdlink.in/4ysm4XS

๐ŸŽฏ Perfect for Students โ€ข Freshers โ€ข Job Seekers โ€ข Aspiring Data Analysts
โค4
๐Ÿš€ ๐—ง๐—”๐—ง๐—” ๐—š๐—ฟ๐—ผ๐˜‚๐—ฝ ๐—™๐—ฅ๐—˜๐—˜ ๐—ฉ๐—ถ๐—ฟ๐˜๐˜‚๐—ฎ๐—น ๐—œ๐—ป๐˜๐—ฒ๐—ฟ๐—ป๐˜€๐—ต๐—ถ๐—ฝ ๐—ฃ๐—ฟ๐—ผ๐—ด๐—ฟ๐—ฎ๐—บ๐˜€ ๐Ÿ˜

Tata Group/TCS virtual job simulations let you work through industry-style tasks and strengthen your resume.

๐ŸŽ“ 3 FREE Virtual Programs:
๐Ÿ“Š Data Visualisation
๐Ÿ” Cybersecurity
๐ŸŒฑ ESG (Environmental, Social & Governance)

๐Ÿ’ป Virtual & flexible
๐ŸŽ“ Free Certificate on Completion
๐Ÿ“„ Add the experience to your Resume/LinkedIn

๐Ÿ”— ๐—˜๐—ป๐—ฟ๐—ผ๐—น๐—น ๐—ณ๐—ผ๐—ฟ ๐—™๐—ฅ๐—˜๐—˜ ๐Ÿ‘‡:-

https://pdlink.in/4yoXEOI

๐Ÿ”ฅ Perfect for Students โ€ข Freshers โ€ข Job Seekers
๐Ÿš€ Data Analyst Roadmap โ€” Part 19

๐Ÿง  SQL Level 9 โ€” Query Performance & Optimization

Knowing how to write a SQL query is one skill. Knowing how to write a query that is correct, efficient, scalable, and easy to maintain is another.

As a Data Analyst, you may eventually work with tables containing:

๐Ÿ“Š Millions of rows

๐Ÿ“ฆ Large transaction datasets

๐Ÿ‘ฅ Millions of customers

๐Ÿ“… Years of historical data

A query that works perfectly on 10,000 rows may become extremely slow on 100 million rows. That's why understanding SQL performance matters.

๐Ÿ”น 1. What Is Query Optimization?

Query optimization means improving a query so that it:

โœ… Returns the correct result

โœ… Uses fewer resources

โœ… Processes less unnecessary data

โœ… Executes faster

โœ… Scales better as data grows

๐Ÿ”น 2. Select Only the Columns You Need

Avoid:

SELECT * FROM Orders;


when you only need:

SELECT Order_ID, Customer_ID, Sales FROM Orders;


SELECT * can:

โ€ข Read unnecessary columns

โ€ข Increase data transfer

โ€ข Make queries harder to maintain

โ€ข Become problematic when tables change

For analytical work, explicitly selecting required columns is usually better.

๐Ÿ”น 3. Filter Data Early

Suppose you only need 2026 orders:

SELECT Customer_ID, SUM(Sales) AS Total_Sales 
FROM Orders
WHERE Order_Date >= '2026-01-01'
AND Order_Date < '2027-01-01'
GROUP BY Customer_ID;


Filtering reduces the amount of data that later operations need to process.

๐Ÿ”น 4. Understand Indexes

An index is a database structure that can help locate rows more efficiently. Think of it like the index of a book.

Without an index: Database โ†’ potentially scan many rows

With a useful index: Database โ†’ locate relevant data more efficiently

Indexes can be especially useful for columns frequently used in: WHERE, JOIN, ORDER BY, and sometimes GROUP BY.

Example:

CREATE INDEX idx_orders_customer ON Orders(Customer_ID);


โš ๏ธ Index syntax and behavior vary by database.

๐Ÿ”น 5. Indexes Are Not Always Better

Indexes have costs. They can:

Consume storage, slow down INSERT, UPDATE, DELETE, and require maintenance.

Therefore:

Don't create indexes on every column. Database systems and workloads determine which indexes are useful.

๐Ÿ”น 6. Index Columns Used for JOINs

SELECT c.Customer_Name, o.Sales 
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID;


The join depends on: Customer_ID.

Appropriate indexing can improve join performance, especially for large tables. But the database optimizer decides whether an index actually provides a benefit.

๐Ÿ”น 7. Avoid Functions on Filtered Columns When Possible

Consider:

WHERE YEAR(Order_Date) = 2026


A range condition is often preferable:

WHERE Order_Date >= '2026-01-01' AND Order_Date < '2027-01-01'


Why?

Applying a function to every row can make it harder for some database systems to use an index efficiently. This is called maintaining sargability.

๐Ÿ”น 8. What Is Sargability?

A condition is generally considered sargable when the database can efficiently use an index to find matching rows.

Compare:

WHERE Order_Date >= '2026-01-01' 


with:

WHERE YEAR(Order_Date) = 2026


The first form usually gives the optimizer more opportunity to use an index on Order_Date.

๐Ÿ”น 9. Use "EXPLAIN"

One of the most important tools for understanding query performance is:

EXPLAIN 
SELECT * FROM Orders WHERE Customer_ID = 1001;
โค2
Depending on the database, you may also see:

EXPLAIN ANALYZE


These tools can show how the database plans to execute or actually executes the query.

You may encounter concepts such as: Table scan, Index scan, Index seek, Join strategy, Sort, Aggregate, Estimated rows, Actual rows, Execution cost.

๐Ÿ”น 10. Table Scan vs Index Access

A table scan may require reading a large portion of a table. An index-based access path can sometimes locate matching records much more efficiently.

But: โš ๏ธ A table scan is not automatically bad.

If you need most of the table, scanning it may actually be the best strategy.

The optimizer chooses based on: Data size + statistics + indexes + query conditions + database engine.

๐Ÿ”น 11. Avoid Unnecessary "DISTINCT"

This query:

SELECT DISTINCT Customer_ID FROM Orders; 


may be perfectly valid.

But don't use DISTINCT simply to hide duplicate rows created by an incorrect join.

๐Ÿ”น 12. Be Careful With JOIN Multiplication

Suppose:

Customer A โ†’ 10 Orders and each order has 5 Order Items

Joining customers โ†’ orders โ†’ order items can produce many rows.

If you then calculate: SUM(Order_Sales) you could accidentally multiply the order-level sales.

This is a logic problem, not just a performance problem. Always understand the grain before joining and aggregating.

๐Ÿ”น 13. "UNION" vs "UNION ALL"

"UNION" removes duplicates.

SELECT Customer_ID FROM Current_Customers 
UNION
SELECT Customer_ID FROM Previous_Customers;


"UNION ALL" simply combines the results:

SELECT Customer_ID FROM Current_Customers 
UNION ALL
SELECT Customer_ID FROM Previous_Customers;


If you don't need duplicate removal, UNION ALL is generally preferable because it avoids the additional deduplication work.

๐Ÿ”น 14. Avoid Repeating Expensive Logic

Suppose the same complex calculation appears multiple times.

Instead of repeating it, consider using: CTEs, Derived tables, Temporary tables, Views, Pre-aggregated tables.

The objective is: Calculate expensive logic only when necessary.

๐Ÿ”น 15. CTEs and Performance

CTEs make SQL much easier to organize:

WITH Customer_Sales AS (
SELECT Customer_ID, SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
)
SELECT * FROM Customer_Sales WHERE Total_Sales > 10000;


A CTE is primarily a query organization technique. Whether it improves performance depends on the database engine, query structure, materialization behavior, and optimizer.

๐Ÿ”น 16. Reduce Data Before Expensive Operations

A useful analytical pattern is:

Raw Data โ†’ Filter โ†’ Select Required Columns โ†’ Join โ†’ Aggregate โ†’ Window Function โ†’ Final Result

The exact optimal order depends on the query, but the principle is: Don't process more data than necessary.

๐Ÿ”น 17. Avoid Correlated Subqueries When a Better Approach Exists

A correlated subquery may conceptually execute in relation to each outer row:

SELECT e.Employee, e.Salary 
FROM Employees e
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.Department = e.Department
);


Sometimes a CTE + join or window function can express the same business logic more efficiently:

WITH Employee_Data AS (
SELECT
Employee,
Department,
Salary,
AVG(Salary) OVER (PARTITION BY Department) AS Avg_Dept_Salary
FROM Employees
)
SELECT Employee, Department, Salary
FROM Employee_Data
WHERE Salary > Avg_Dept_Salary;
โค5
๐Ÿ”น 18. Query Readability Also Matters

A query can be technically fast but difficult to understand.

Bad analytical SQL often contains:

โŒ Unclear aliases, repeated logic, huge nested queries, unnecessary columns, unnecessary joins, no explanation of business logic

Good SQL should be:

โœ… Correct, efficient, readable, maintainable, easy to troubleshoot

๐Ÿ”น 19. SQL Optimization Checklist

Before finalizing a query, ask:

1. Do I need all these columns?

2. Am I processing unnecessary rows?

3. Are my joins using the correct keys?

4. Did the join change the grain?

5. Am I accidentally multiplying values?

6. Can a date filter be written as a range?

7. Do I really need "DISTINCT"?

8. Could "UNION ALL" be used instead of "UNION"?

9. Can I inspect the execution plan?

10. Will this query still perform well on a much larger dataset?

๐ŸŽฏ SQL Interview Challenge

Question:

A query takes 30 seconds:

SELECT * FROM Orders WHERE YEAR(Order_Date) = 2026;


How could you improve it?

A better approach is:

SELECT Order_ID, Customer_ID, Order_Date, Sales 
FROM Orders
WHERE Order_Date >= '2026-01-01'
AND Order_Date < '2027-01-01';


Why?

โœ” Avoids unnecessary columns

โœ” Uses a range filter

โœ” Can be more index-friendly

โœ” Clearly defines the required period

Then use EXPLAIN/EXPLAIN ANALYZE to verify the actual execution plan.

๐Ÿง  Double Tap โค๏ธ For More
โค5๐Ÿ‘2
๐Ÿš€ ๐—ง๐—ผ๐—ฝ ๐—ง๐—ฒ๐—ฐ๐—ต ๐—–๐—ฒ๐—ฟ๐˜๐—ถ๐—ณ๐—ถ๐—ฐ๐—ฎ๐˜๐—ถ๐—ผ๐—ป๐˜€ ๐˜๐—ผ ๐—Ÿ๐—ฎ๐—ป๐—ฑ ๐—›๐—ถ๐—ด๐—ต-๐—ฃ๐—ฎ๐˜†๐—ถ๐—ป๐—ด ๐—๐—ผ๐—ฏ๐˜€ ๐—ถ๐—ป ๐Ÿฎ๐Ÿฌ๐Ÿฎ๐Ÿฒ๐Ÿ˜

๐Ÿ’ฐ Highest Salary: โ‚น41 LPA
๐Ÿ“ˆ Average Salary: โ‚น7.4 LPA
๐ŸŽ“ 2,000+ Students Placed
๐Ÿข 500+ Hiring Partners

๐Ÿ’ป Full Stack :- https://pdlink.in/3SuUeuD

๐Ÿ“Š Data Analytics :- https://pdlink.in/45vk5ph

๐Ÿ’ซAI Engineering :- https://pdlink.in/4fWJVID

๐Ÿ”ฅ Take the first step towards your high-paying tech career in 2026!
โค1
๐——๐—ฎ๐˜๐—ฎ ๐—ฆ๐—ฐ๐—ถ๐—ฒ๐—ป๐—ฐ๐—ฒ ๐—™๐—ฅ๐—˜๐—˜ ๐—ข๐—ป๐—น๐—ถ๐—ป๐—ฒ ๐— ๐—ฎ๐˜€๐˜๐—ฒ๐—ฟ๐—ฐ๐—น๐—ฎ๐˜€๐˜€ ๐Ÿ˜

๐Ÿ’ซAccelerate your career in Data Science

๐Ÿ’ซDiscover the skills, tools and career roadmap needed to enter this high-demand field.

๐Ÿ”ฅ Beginner-friendly online sessionโ€”no prior experience required!

๐—ฅ๐—ฒ๐—ด๐—ถ๐˜€๐˜๐—ฒ๐—ฟ ๐—™๐—ผ๐—ฟ ๐—™๐—ฅ๐—˜๐—˜ ๐Ÿ‘‡:-

https://pdlink.in/46adC3l

(Only few slots left )

๐Ÿ“… Date: September 11, 2026
โฐ Time: 7:00 PM
๐Ÿš€ Data Analyst Roadmap โ€” Part 20

๐Ÿง  SQL Level 10 โ€” Cohort Analysis, Retention & Customer Analytics

Now we're moving from writing SQL queries to using SQL for real analytical problems.

Customer analytics is one of the most important areas because businesses want to know:

๐Ÿ‘ฅ Who are our customers?

๐Ÿ›’ When did they first purchase?

๐Ÿ”„ Do they come back?

๐Ÿ“‰ When do they stop returning?

๐Ÿ’ฐ Which customers generate most revenue?

๐Ÿ“Š How does behavior change over time?

One of the most powerful techniques for this is Cohort Analysis.

๐Ÿ”น 1. What Is Cohort Analysis?

A cohort is a group who share a common starting point.

โ€ข Jan 2026 Cohort = first purchase in Jan 2026

โ€ข Feb 2026 Cohort = first purchase in Feb 2026

Instead of mixing everyone, we track each group over time.

๐Ÿ”น 2. Why It Matters

Suppose total monthly customers are increasing.

That sounds positive.

But what if new customers are increasing while existing customers stop returning?

A simple monthly report may hide this problem.

Cohort analysis separates New vs Returning customers.

This makes retention problems much easier to identify.

๐Ÿ”น 3. Step 1 โ€” Find Each Customer's First Purchase

SELECT
Customer_ID,
MIN(Order_Date) AS First_Order_Date
FROM Orders
GROUP BY Customer_ID;


This gives us the first purchase date for every customer.

Customer | First Order
C101 | 2026-01-10
C102 | 2026-01-18
C103 | 2026-02-05


๐Ÿ”น 4. Step 2 โ€” Assign a Cohort Month

We can convert the first purchase into a month-level cohort.

First Purchase Date โ†’ Cohort Month
C101 โ†’ 2026-01
C103 โ†’ 2026-02


The exact month-truncation syntax varies between SQL databases.

๐Ÿ”น 5. Step 3 โ€” Join Cohort Back to Orders

Now we need both:

Customer's cohort

and

Customer's subsequent activity

WITH Customer_Cohorts AS (
SELECT Customer_ID, MIN(Order_Date) AS First_Order_Date
FROM Orders GROUP BY Customer_ID
)
SELECT o.Customer_ID, c.First_Order_Date, o.Order_Date, o.Sales
FROM Orders o
JOIN Customer_Cohorts c ON o.Customer_ID = c.Customer_ID;


Now every transaction knows which cohort the customer belongs to.

๐Ÿ”น 6. Cohort Month vs Activity Month

โ€ข Cohort Month: When first purchased

โ€ข Activity Month: When purchase happened

Customer | Cohort | Activity
C101 | Jan | Jan
C101 | Jan | Feb
C101 | Jan | Mar


๐Ÿ”น 7. Measuring Retention

Retention measures how many customers from a cohort remain active in later periods.

Retention = Active in Period / Original Cohort * 100

โ€ข Jan cohort: 100 customers

โ€ข Feb: 60 active โ†’ 60%

โ€ข Mar: 40 active โ†’ 40%

๐Ÿ”น 8. Retention Month

Months Since Cohort = Activity - Cohort

Eg:

Customer | Cohort | Activity | Months_Since_Cohort
C101 | Jan | Jan | 0
C101 | Jan | Feb | 1
C101 | Jan | Mar | 2


๐Ÿ”น 9. Cohort Retention Matrix

Conceptually, the final result may look like:

Cohort | Month0 | Month1 | Month2 | Month3
Jan | 100% | 60% | 40% | 30%
Feb | 100% | 65% | 45% | โ€”
Mar | 100% | 70% | โ€” | โ€”


This is often called a cohort retention matrix.

It immediately shows whether newer customer cohorts are retaining better or worse.

๐Ÿ”น 10. Customer Lifetime Value (CLV)

Another important customer metric is Customer Lifetime Value (CLV/LTV).

A simplified version can be based on:

Total Revenue Generated by Customer

A more advanced business model may consider:

โ€ข Revenue

โ€ข Gross margin

โ€ข Purchase frequency

โ€ข Retention

โ€ข Customer lifespan

โ€ข Acquisition cost

๐Ÿ”น 11. Average Order Value (AOV)

A basic customer metric is:

Average Order Value = Total Sales รท Number of Orders

In SQL:
โค2
SELECT Customer_ID, SUM(Sales) / COUNT(DISTINCT Order_ID) AS AOV
FROM Orders GROUP BY Customer_ID;


This tells us how much a customer spends per order on average.

๐Ÿ”น 12. Purchase Frequency

We can also calculate the number of orders per customer:

SELECT Customer_ID, COUNT(DISTINCT Order_ID) AS Number_of_Orders
FROM Orders GROUP BY Customer_ID;


Customers can then be segmented based on activity.

For example:

โ€ข 1 order โ†’ One-time customer

โ€ข 2โ€“5 orders โ†’ Repeat customer

โ€ข 6+ orders โ†’ Highly active customer

โš ๏ธ These thresholds are business rules, not universal definitions.

๐Ÿ”น 13. Recency

SELECT Customer_ID, MAX(Order_Date) AS Last_Order_Date
FROM Orders GROUP BY Customer_ID;


Then compare the last order date with a chosen analysis date.

A customer who purchased recently is generally more active than someone whose last purchase was a long time ago.

๐Ÿ”น 14. RFM Analysis

โ€ข R โ†’ Recency: How recently?

โ€ข F โ†’ Frequency: How often?

โ€ข M โ†’ Monetary: How much?

Example:

Customer | Recency | Frequency | Monetary
C101 | 5 days | 12 orders | โ‚น85,000
C102 | 20 days | 6 orders | โ‚น42,000
C103 | 120 days| 2 orders | โ‚น8,000


This allows businesses to identify:

โญ High-value customers

๐Ÿ”„ Loyal customers

โš ๏ธ Customers at risk

๐Ÿ’ค Inactive customers

๐Ÿ”น 15. Segmentation With CASE

You can convert analytical metrics into business segments.

For example:

SELECT Customer_ID, Total_Sales,
CASE
WHEN Total_Sales >= 50000 THEN 'High Value'
WHEN Total_Sales >= 20000 THEN 'Medium Value'
ELSE 'Low Value'
END AS Customer_Segment
FROM Customer_Sales;


This transforms numerical analysis into a business-friendly classification.

๐Ÿ”น 16. Repeat Customers

SELECT Customer_ID, COUNT(DISTINCT Order_ID) AS Order_Count
FROM Orders GROUP BY Customer_ID
HAVING COUNT(DISTINCT Order_ID) > 1;


This finds customers with more than one order.

๐Ÿ”น 17. First vs Repeat Purchase

You can use ROW_NUMBER() to identify purchase sequence.

WITH Customer_Orders AS (
SELECT Customer_ID, Order_ID, Order_Date,
ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date) AS Purchase_Number
FROM Orders
)
SELECT * FROM Customer_Orders;


Now:

Purchase_Number = 1 means the customer's first purchase.

Purchase_Number = 2 means the second purchase.

And so on.

This opens the door to deeper customer behavior analysis.

๐Ÿ”น 18. Time Between Purchases

SELECT Customer_ID, Order_Date,
LAG(Order_Date) OVER (PARTITION BY Customer_ID ORDER BY Order_Date) AS Previous_Order_Date
FROM Orders;


Now you can calculate the number of days between purchases.

โ†’ Helps answer "How frequently do customers return?"

๐Ÿ”น 19. Churn Analysis

Churn means customers stop using or purchasing from a business.

SQL can help identify customers whose activity has fallen below a defined threshold.

For example:

Last Purchase โ†’ Days Since โ†’ Business Threshold โ†’ Active / At Risk / Inactive

SQL finds pattern, business defines churn.

๐ŸŽฏ Interview Challenge

Find customers with โ‰ฅ3 orders and >50,000 spent:

SELECT Customer_ID, COUNT(DISTINCT Order_ID) AS Order_Count, SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID
HAVING COUNT(DISTINCT Order_ID) >= 3 AND SUM(Sales) > 50000;


๐Ÿง  Double Tap โค๏ธ For More
โค6
๐—ง๐—ผ๐—ฝ ๐Ÿฑ ๐—™๐—ฅ๐—˜๐—˜ ๐—–๐—ผ๐˜‚๐—ฟ๐˜€๐—ฒ๐˜€ ๐˜๐—ผ ๐—ž๐—ถ๐—ฐ๐—ธ๐˜€๐˜๐—ฎ๐—ฟ๐˜ ๐—ฌ๐—ผ๐˜‚๐—ฟ ๐——๐—ฎ๐˜๐—ฎ ๐—ฆ๐—ฐ๐—ถ๐—ฒ๐—ป๐—ฐ๐—ฒ ๐—–๐—ฎ๐—ฟ๐—ฒ๐—ฒ๐—ฟ ๐Ÿ“Š

Want to start a career in Data Science without spending money?

Here are 5 beginner-friendly learning resources covering essential skills such as Python, SQL, Machine Learning and hands-on projects.

๐Ÿ”— ๐—˜๐—ป๐—ฟ๐—ผ๐—น๐—น ๐—ณ๐—ผ๐—ฟ ๐—™๐—ฅ๐—˜๐—˜ ๐Ÿ‘‡:-

https://pdlink.in/4ilAmok

๐ŸŽฏ Perfect for Students โ€ข Freshers โ€ข Beginners โ€ข Aspiring Data Scientists

๐Ÿ’ก Learn โ†’ Practice โ†’ Build Projects โ†’ Create Your Portfolio