Data Analytics SQL Assessment

Overview

This repository contains SQL solutions to a four-question assessment designed to evaluate analytical and technical skills using real-world business scenarios.

Tables Used:
users_customuser, plans_plan, savings_savingsaccount, withdrawals_withdrawal

---

Questions & Approach

Question 1: High-Value Customers

Objective: Identify customers with at least one savings and one investment plan, and rank them by total confirmed deposits.

Approach:

• Joined users_customuser, plans_plan, and savings_savingsaccount.
• Used conditional aggregation to count savings and investment plans.
• Filtered customers to include only those with both plan types.
• Aggregated confirmed deposits (in kobo) and converted to naira using /100.
• Constructed fallback-safe names using COALESCE.
• Sorted results by total deposits in descending order.

---

Question 2: Transaction Frequency

Objective: Categorize customers into High, Medium, or Low frequency users based on withdrawal behavior.

Approach:

• Aggregated total transactions per customer from withdrawals_withdrawal.
• Calculated active months using the difference between first and last transaction dates.
• Derived average monthly transaction rates.
• Used CASE statements to classify customers into:
  • High Frequency: ≥ 10 txns/month
  • Medium Frequency: 3–9 txns/month
  • Low Frequency: < 3 txns/month
• Aggregated results by frequency category.

---

Question 3: Inactive Accounts

Objective: Find savings or investment plans with no activity for over 365 days.

Approach:

• Joined plans_plan with withdrawals_withdrawal on owner_id.
• Identified last transaction per plan; defaulted to created_on if none exist.
• Calculated inactivity in days using DATEDIFF(CURDATE(), ...).
• Used HAVING to filter accounts inactive for more than a year.
• Classified plans as 'Savings' or 'Investment' using CASE.

---

Question 4: Customer Lifetime Value (CLV)

Objective: Estimate CLV based on tenure, transactions, and total value.

Approach:

• Calculated customer tenure in months using TIMESTAMPDIFF.
• Aggregated total transaction count and value (in kobo) from withdrawals_withdrawal.
• Combined metrics to apply a simplified CLV formula: [ \text{CLV} = \left(\frac{\text{total transactions}}{\text{tenure}}\right) \times 12 \times 0.001 \times \text{total value} ] (Note: 0.001 accounts for kobo to naira conversion)
• Constructed names and ordered customers by estimated CLV.

---

Challenges Faced

• Monetary fields were stored in kobo, requiring conversion to naira in all relevant calculations.
• Ensuring NULL-safe name display using COALESCE and TRIM.
• Classifying plans and frequency segments accurately with edge-case handling.
• Balancing performance with clarity in multi-step CTE queries.

---

Author

Henry C. Dibie
GitHub | henrymorgan273@yahoo.com