How Uncodemy Trains Students for Business Analyst Roles

-- monthly_active_users.sql — a real BA query
-- The kind of analysis every BA writes in the first month on the job.

WITH monthly_activity AS (
    SELECT
        DATE_TRUNC('month', event_time) AS month,
        COUNT(DISTINCT user_id) AS active_users
    FROM events
    WHERE event_name IN ('login', 'purchase', 'view_product')
      AND event_time >= '2025-01-01'
    GROUP BY 1
),
with_change AS (
    SELECT
        month,
        active_users,
        LAG(active_users) OVER (ORDER BY month) AS prev_active
    FROM monthly_activity
)
SELECT
    month,
    active_users,
    prev_active,
    ROUND(
        (active_users - prev_active) * 100.0 / NULLIF(prev_active, 0),
        2
    ) AS growth_pct
FROM with_change
ORDER BY month;

-- Why a BA writes this:
-- - Product and business teams want month-over-month growth.
-- - The BA translates this question into a query.
-- - The query becomes a weekly/monthly business review artefact.
--
-- What interviewers look for:
-- - Do you define "active user" consistently?
-- - Can you reason about why growth slowed or spiked?
-- - Do you handle NULLs and new months cleanly?

# user_story.md — the kind of documentation a BA writes
# Structured, testable, and ready for a development team.

## Story

**Title:** Let customers save payment methods for faster checkout

**As a** returning customer
**I want** to save my payment method after checkout
**So that** I can check out faster next time without re-entering details

---

## Acceptance Criteria

- [ ] Given the user completes a successful checkout, they see a "Save this card" checkbox, checked by default
- [ ] When they uncheck the box, the card is not saved
- [ ] Saved cards appear at the top of the payment step in future checkouts
- [ ] Users can delete a saved card from "Payment methods" in account settings
- [ ] No card numbers are ever stored in plain text; only a tokenised reference is saved

---

## Business Rules

- Saving is optional; users must be able to opt out
- Saved cards must be validated against the payment provider's tokenisation rules
- Deletion must remove the card immediately from future checkouts

---

## Edge Cases

- Payment provider unavailable — user sees a clear error and the card is not saved
- User has reached the maximum saved cards limit (5)
- User changes their default payment method after saving

---

## Why this matters:
- The BA translates business intent into a story developers can implement.
- Acceptance criteria are testable — QA can verify directly.
- Edge cases prevent surprises after launch.

---

## What interviewers look for:
- Is the story specific, testable, and business-focused?
- Are edge cases considered?
- Do acceptance criteria map to real behaviour?

// Power BI measures — what a BA builds for business dashboards
// A small set of measures covers most business reporting needs.

Total Revenue = SUM ( Sales[amount] )

Total Orders = COUNTROWS ( Sales )

Average Order Value = DIVIDE ( [Total Revenue], [Total Orders] )

Active Customers = DISTINCTCOUNT ( Sales[customer_id] )

Revenue Previous Month =
CALCULATE (
    [Total Revenue],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

Revenue Growth % =
DIVIDE (
    [Total Revenue] - [Revenue Previous Month],
    [Revenue Previous Month]
)

Conversion Rate =
DIVIDE (
    COUNTROWS ( FILTER ( Sales, Sales[stage] = "Won" ) ),
    COUNTROWS ( Sales )
)

// Why these measures matter:
// - They are the KPIs business stakeholders ask about every week.
// - They are reusable across every report.
// - They demonstrate that the BA understands business metrics, not just visuals.

// What interviewers look for:
// - Do you know CALCULATE and time intelligence?
// - Can you explain each KPI in business terms?
// - Do you handle division by zero safely (DIVIDE)?