Goal: Identify customers who have both a savings and an investment plan.
Approach: Used two joins to the plans_plan table for savings and investment criteria. Summed confirmed deposits.
Goal: Categorize customers into frequency tiers based on average monthly transaction count. Approach: Calculated transaction counts and active months, averaged them and grouped into High, Medium, and Low frequency categories.
Goal: Identify savings or investment accounts with no transactions in the last 365 days. Approach: Calculated last transaction date per user and computed inactivity days from the current date.
Goal: Estimate Customer Lifetime Value (CLV). Approach: Calculated tenure from signup date, total transactions and derived CLV using provided formula.
- Time Intervals: Approximating months from date differences while ensuring edge cases (e.g., 0-month tenure) were handled.
- Amount Units: All currency fields were in kobo, requiring conversion to naira by dividing by 100.
- Join Complexity: Q1 required careful handling of multiple joins to the same table (aliasing for clarity).