DataAnalytics-Assessment

This repository contains solutions to a SQL-based assessment designed to evaluate data analysis proficiency using relational databases.

---

Question-wise Approach

Q1: High-Value Customers with Multiple Products

Approach:
Identified customers with at least one savings and one investment product using joins and filters on is_regular_savings and is_a_fund. Aggregated their total deposits and sorted descending.

Q2: Transaction Frequency Analysis

Approach:
Counted total transactions per customer and calculated number of active months using transaction dates. Classified into frequency categories using a CASE statement.

Q3: Account Inactivity Alert

Approach:
Pulled the last transaction date per account, calculated days since last transaction, and filtered for inactivity greater than 365 days.

Q4: Customer Lifetime Value (CLV)

Approach:
Used a simplified formula considering transaction volume and tenure in months. Handled potential divide-by-zero and converted Kobo to Naira for final CLV estimation.

---

Challenges

• Currency Conversion: Needed to convert Kobo to Naira by dividing by 100.
• Tenure Calculation: Used PostgreSQL AGE() and DATE_PART() functions to calculate active months accurately.
• Data Absence: Handled cases where some customers had no transactions using LEFT JOIN and COALESCE.
• Efficiency: Optimized CTEs to avoid redundant aggregations and used filtering early.

---

Note

All queries are written in PostgreSQL syntax. You may need to tweak functions if using a different SQL dialect.