Sumaya Mateen

Problem

Dana Park at Nexus Financial (simulated retail bank) wanted to test whether customers who experienced fraud were more likely to churn than those who did not. The task required calculating customer counts and churn rates for two separate cohorts, fraud-affected and fraud-free, scoped to customers who onboarded in 2024.

What I did

Churn detection was built as a layered CASE expression combining three independent signals: a non-null churn_date on the customer record, a matching event in the customer_lifecycle table with type churned or closed, and the fallback condition of all accounts being closed with none remaining active. This multi-signal approach avoids undercounting churn when any single field is inconsistently populated. Scoping to 2024 onboarders was applied via EXTRACT(YEAR FROM onboarding_date) = 2024 in the fraud_status CTE. A LEFT JOIN on fraud_cases with a COUNT(case_id) greater than zero check cleanly separates the two cohorts without dropping any customers.

Outcome

Dana received a direct, side-by-side comparison of fraud and non-fraud churn rates for the 2024 cohort, giving the team a quantified starting point for evaluating whether the two customer risk stories are linked.

Next step

Segmenting the fraud cohort by fraud case type or loss amount band would show whether it is the occurrence of fraud or its severity that drives the higher churn signal.

Problem

The head of analytics at Meridian Retail (simulated retailer) needed to show the board how customer lifetime value evolves after acquisition. The specific question was: for customers acquired in a given quarter, what percentage of their first-quarter revenue is retained in each subsequent quarter? The matrix needed to cover eight quarters and exclude cancelled, returned, and refunded orders.

What I did

Status filtering happens in the first CTE, before any aggregation, so cancelled, returned, and refunded orders never enter the cohort calculations. Cohort assignment uses DATE_TRUNC on the customer's first order date rather than the order date being analysed, which keeps each customer anchored to their true acquisition quarter. The cohort age formula, year-difference multiplied by four plus quarter-difference, handles year boundaries correctly, so a customer acquired in Q4 2022 who orders in Q1 2023 receives an age of one, not a negative value. NULLIF wraps the Q0 revenue denominator to guard against a division-by-zero error appearing in a board presentation.

Outcome

The board received a single result set with revenue expressed in millions of naira and retention percentages rounded to two decimal places, ready for direct inclusion in a slide deck.

cohort_base AS (
 SELECT
 bo.customer_id,
 bo.revenue_naira,
 DATE_TRUNC('quarter', fo.first_order_date) AS cohort_quarter,
 DATE_TRUNC('quarter', bo.created_at) AS order_quarter,
 (
 (EXTRACT(YEAR FROM DATE_TRUNC('quarter', bo.created_at)) -
 EXTRACT(YEAR FROM DATE_TRUNC('quarter', fo.first_order_date))) * 4
 +
 (EXTRACT(QUARTER FROM DATE_TRUNC('quarter', bo.created_at)) -
 EXTRACT(QUARTER FROM DATE_TRUNC('quarter', fo.first_order_date)))
 ) AS cohort_age
 FROM base_orders bo
 JOIN first_order fo
 ON bo.customer_id = fo.customer_id
)

This CTE anchors every order to the customer's acquisition quarter and computes cohort age in quarters, handling year-boundary arithmetic so that Q4-to-Q1 transitions produce a positive age of one rather than a negative value.

Next step

A useful next step would be to break retention by product category, to see whether certain category buyers drive the stronger cohorts in that 40-83% range.

Problem

David Kim at Nexus Financial (simulated retail bank) needed a single wide-format result set comparing 2023 and 2024 across five metrics: total deposits, loan originations, transaction volume, fraud loss, and customer churn. Each metric required an absolute change and a percentage change column, all in one row.

What I did

Deposits were calculated using point-in-time logic, filtering accounts that opened before year-end and had not yet closed by that date, which captures an end-of-year balance snapshot rather than a period sum. Each of the five metrics was isolated in its own CTE returning a single row, then all ten single-row CTEs were assembled via CROSS JOIN, which is safe here precisely because each CTE produces exactly one row. NULLIF wraps every percentage change denominator to prevent division-by-zero, and COALESCE wraps every aggregate to replace nulls from empty date ranges with zero.

Outcome

David received a single-row result set covering every required metric, formatted for direct inclusion in a board pack without additional pivoting or manual calculation.

deposits_2023 AS (
 SELECT COALESCE(ROUND(SUM(a.current_balance) / 100.0, 2), 0) AS total_deposits
 FROM nexus_banking.accounts a
 WHERE a.status = 'active'
 AND a.opened_date < '2024-01-01'
 AND (a.closed_date IS NULL OR a.closed_date >= '2024-01-01')
),

This CTE captures the end-of-2023 deposit balance by requiring accounts to have opened before 2024 and not yet closed by that date, converting cents to dollars and guarding against a null result with COALESCE.

Next step

Adding a branch-level breakdown of the same five metrics would let David identify which locations are driving the aggregate movements rather than reading only the institution-wide totals.

Problem

David Kim at Nexus Financial (simulated retail bank) required a board-grade Power BI report covering the full business: onboarding, executive overview, churn, fraud, branches, loan portfolio, and liquidity risk. The file had to be openable and fully navigable by David himself, with consistent theming, working bookmarks, and no raw measure names visible as labels.

What I did

The report was built across eight pages, exceeding the five-page minimum, with a pageNavigator visual placed on every substantive page so click-through navigation works without returning to a home screen. A dedicated Tooltip Page was constructed using six card visuals, the correct pattern for surfacing at-a-glance KPIs when a user hovers over key visuals elsewhere in the report. The DAX measure library reaches 81 measures spanning churn, fraud, loans, branches, deposits, quartile banding, and health status signals. A field parameter table with a SELECTEDVALUE measure was wired to the Executive Overview page to satisfy the interactive parameter requirement.

Outcome

David received a zipped PBIP file he could open and click through independently, with a complete branded layout and an 81-measure library covering every domain in the brief.

Next step

Row-level security is not yet configured on this report, so adding RLS roles scoped by branch or region would be the logical next step before sharing the file beyond the core finance team.