I would love to help you prep for this role! It's a fantastic position that sits right at the intersection of tech and business growth at one of Brazil's largest financial institutions.

To make sure we make the absolute best use of your time, let's start with a quick check-in about your current background so I can customize our practice sessions.

How would you rate your current comfort level with SQL, Python, and Power BI? Also, do you have any existing experience with growth or CRM-focused metrics like cohort analysis, RFM, or clustering?

Your Personalized Prep Roadmap

It is awesome that you already have a deep understanding of growth, revenue, and CRM metrics! Honestly, having that business context is often the hardest part to teach, so you are already starting with a massive advantage. For a Pleno-level role at XP Inc., they need someone who can act as the analytical engine of the squad—translating complex client behavior into clear revenue opportunities.

Since you are very comfortable with growth metrics but fairly new to Python, our main goal will be getting you "interview-ready" on the technical side while leveraging your business strengths.

We will start with SQL and Data Modeling, where you will practice writing queries for cohort analysis and RFM segmentation. Then, we will tackle Python—focusing only on what you actually need for this interview, like manipulating data with Pandas and connecting it to Databricks. Finally, we will bring it all together with Power BI storytelling and culture-fit prep for XP.

Let's dive straight into the technical practice. Since you already understand the business side of growth, translating those concepts into raw code is where you will really stand out in the XP Inc. technical round.

Cohort Analysis: The Retention Engine

When XP evaluates a Pleno Analyst, they want to see if you can construct a cohort matrix using SQL. This typically involves a three-step query pattern:

1. Identify the acquisition date (the first purchase date) for every customer.
2. Calculate the time delta (the number of months elapsed) between that first purchase and all subsequent purchases.
3. Aggregate the active users or revenue by the acquisition month and the elapsed months.

Let's look at a concrete, step-by-step SQL pattern using a Common Table Expression (CTE) to calculate this. Imagine we have a table called transactions with columns customer_id, transaction_date, and amount.

WITH user_first_purchase AS (
  -- Step 1: Find the first transaction month for each user
  SELECT 
    customer_id,
    DATE_TRUNC('month', MIN(transaction_date)) AS cohort_month
  FROM transactions
  GROUP BY customer_id
),
cohort_stages AS (
  -- Step 2: Calculate the difference in months for each transaction
  SELECT
    t.customer_id,
    f.cohort_month,
    DATE_TRUNC('month', t.transaction_date) AS active_month,
    -- Calculate elapsed months
    DATEDIFF('month', f.cohort_month, DATE_TRUNC('month', t.transaction_date)) AS period_number,
    t.amount
  FROM transactions t
  JOIN user_first_purchase f ON t.customer_id = f.customer_id
)
-- Step 3: Aggregate active customers and revenue by cohort
SELECT
  cohort_month,
  period_number,
  COUNT(DISTINCT customer_id) AS active_customers,
  SUM(amount) AS total_revenue
FROM cohort_stages
GROUP BY 1, 2
ORDER BY 1, 2;

This query gives us a clean structure showing how many customers from each monthly cohort came back and spent money in Month 0, Month 1, Month 2, and so on.

How does this query feel to you? We can walk through the logic of DATEDIFF and window functions, or we can look at how you would turn these raw numbers into a retention percentage. What would you like to explore next?