SQL Programming Resources on Telegram: “Practice 4 — Rank customers by total spending. W…”

Practice 4 — Rank customers by total spending.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
RANK() OVER ( ORDER BY total_spending DESC ) AS spending_rank
FROM customer_sales;

Practice 5 — Create customer segments based on spending.

WITH customer_sales AS (
    SELECT customer_id, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spending,
CASE WHEN total_spending >= 100000 THEN 'VIP'
     WHEN total_spending >= 50000 THEN 'Premium'
     ELSE 'Standard' END AS segment
FROM customer_sales;

🧪 Mini SQL Challenge

You have: orders(order_id, customer_id, amount, order_date)

Find the top 5 customers by total spending, but only consider customers who have placed at least 3 orders.

Solution:

WITH customer_metrics AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spending
    FROM orders GROUP BY customer_id
),
qualified_customers AS (
    SELECT customer_id, order_count, total_spending
    FROM customer_metrics WHERE order_count >= 3
)

SELECT customer_id, order_count, total_spending
FROM qualified_customers ORDER BY total_spending DESC LIMIT 5;

Logic:

Orders ↓

GROUP BY customer ↓

Calculate order count + spending ↓

Keep customers with ≥ 3 orders ↓

Sort by spending ↓

Return top 5

💡 Double Tap ❤️ For More