Data Analytics on Telegram: “SELECT Customer_ID, SUM(Sales) / COUNT(DISTINCT…”

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