SQL Data Analysis Challenge for InsureTech Skills

At InsureTech Skills, we believe in empowering data professionals with real-world SQL challenges to sharpen their analytical skills. In this blog, we explore an Eight-Week SQL Challenge by working through customer nodes and transaction data, answering key business questions.

Database Setup

---

Section A: Customer Nodes Exploration

1. How many unique nodes exist in the Data Bank system?

2. What is the number of nodes per region?

3. How many customers are allocated to each region?

4. How many days on average are customers reallocated to a different node?

5. What is the median, 80th, and 95th percentile for reallocation days per region?

WITH reallocation AS (
    SELECT r.region_id, r.region_name, DATEDIFF(DAY, start_date, end_date) AS day_diff
    FROM customer_nodes c
    JOIN regions r ON r.region_id = c.region_id
    WHERE end_date != '99991231'
)
SELECT DISTINCT region_id, region_name,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY day_diff) OVER (PARTITION BY region_name) AS median,
    PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY day_diff) OVER (PARTITION BY region_name) AS percentile_80,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY day_diff) OVER (PARTITION BY region_name) AS percentile_95
FROM reallocation
ORDER BY region_id;

---

Section B: Customer Transactions

1. What is the unique count and total amount for each transaction type?

2. What is the average total historical deposit counts and amounts for all customers?

WITH cte AS (
    SELECT customer_id, COUNT(*) AS count_transactions, SUM(txn_amount) AS total_amount
    FROM customer_transactions WHERE txn_type = 'deposit'
    GROUP BY customer_id
)
SELECT AVG(count_transactions), AVG(total_amount) FROM cte;

3. How many customers make more than 1 deposit and at least 1 purchase or withdrawal per month?

4. What is the closing balance for each customer at the end of the month?

WITH balance_calc AS (
    SELECT customer_id, MONTH(txn_date) AS month,
        SUM(CASE WHEN txn_type = 'deposit' THEN txn_amount ELSE -txn_amount END) AS balance
    FROM customer_transactions
    GROUP BY customer_id, MONTH(txn_date)
)
SELECT customer_id, month, SUM(balance) OVER (PARTITION BY customer_id ORDER BY month ASC) AS Closing_balance
FROM balance_calc
ORDER BY customer_id, month;

5. What percentage of customers increased their closing balance by more than 5%?

WITH balance_change AS (
    SELECT customer_id, month, SUM(balance) OVER (PARTITION BY customer_id ORDER BY month ASC) AS Closing_balance,
        LAG(SUM(balance) OVER (PARTITION BY customer_id ORDER BY month ASC)) OVER (PARTITION BY customer_id ORDER BY month) AS prev_balance
    FROM balance_calc
)
SELECT ROUND(
    CAST(COUNT(DISTINCT customer_id) * 100.0 / (SELECT COUNT(DISTINCT customer_id) FROM customer_transactions) AS FLOAT), 2
) AS percent_customers
FROM balance_change
WHERE Closing_balance > prev_balance * 1.05;

Conclusion

By analyzing customer nodes and transaction patterns, businesses can optimize customer allocations, understand transaction behaviors, and identify trends in financial activities. At InsureTech Skills, we provide hands-on training in SQL, Data Analysis, and Business Intelligence to help professionals upskill in real-world scenarios.

For more SQL challenges and interview questions, visit InsureTech Skills. Stay tuned for more insights!