Can a BCA Student Become a Data Analyst Without a Degree?

-- monthly_revenue.sql — a real analyst query
-- Combines joins, aggregation, and window functions — the core of
-- most data analyst interviews.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', o.order_date) AS month,
        c.region,
        COUNT(DISTINCT o.customer_id) AS customers,
        COUNT(*) AS orders,
        SUM(o.amount) AS revenue
    FROM orders o
    JOIN customers c
        ON o.customer_id = c.customer_id
    WHERE o.order_date >= '2025-01-01'
      AND o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY 1, 2
),
with_growth AS (
    SELECT
        month,
        region,
        customers,
        orders,
        revenue,
        LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue
    FROM monthly
)
SELECT
    month,
    region,
    customers,
    orders,
    revenue,
    prev_revenue,
    ROUND(
        (revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0),
        2
    ) AS growth_pct
FROM with_growth
ORDER BY month, region;

-- What interviewers look for:
-- - Do you know how to filter before aggregating?
-- - Can you use window functions (LAG) to compute growth?
-- - Do you handle NULLs and divisions by zero?
--
-- What a clean answer demonstrates:
-- - You think in data workflows, not single queries.
-- - You can write readable, maintainable SQL.
-- - You know when to use a CTE vs a subquery.

# cohort_analysis.py — a Pandas analysis for a real analyst portfolio
# Uses the same thinking BCA already taught you — applied to analytics.

import pandas as pd
import matplotlib.pyplot as plt

# 1. Load the data
df = pd.read_csv("orders.csv", parse_dates=["order_date"])

# 2. Clean
df = df.dropna(subset=["customer_id", "order_date", "amount"])
df = df[df["amount"] >= 0]

# 3. Define each customer's cohort (first order month)
df["order_month"] = df["order_date"].dt.to_period("M")
first_order = df.groupby("customer_id")["order_month"].min()
df["cohort"] = df["customer_id"].map(first_order)

# 4. Compute cohort retention
df["cohort_index"] = (
    (df["order_month"] - df["cohort"]).apply(lambda x: x.n)
)
retention = (
    df.groupby(["cohort", "cohort_index"])["customer_id"]
      .nunique()
      .reset_index()
)
cohort_size = retention[retention["cohort_index"] == 0].set_index("cohort")["customer_id"]
retention["retention_pct"] = (
    retention.apply(lambda row: row["customer_id"] / cohort_size[row["cohort"]] * 100, axis=1)
)

# 5. Visualise
pivot = retention.pivot(index="cohort", columns="cohort_index", values="retention_pct")
pivot.iloc[:, :6].plot(kind="line", marker="o")
plt.title("Monthly cohort retention")
plt.ylabel("Retention %")
plt.tight_layout()
plt.savefig("cohort_retention.png")

# What interviewers look for:
# - Can you translate a business question (retention) into code?
# - Do you clean data before analysing it?
# - Can you visualise insights, not just numbers?
#
# What this portfolio piece demonstrates:
# - Real analytics thinking, not just pandas syntax.
# - The kind of output a data analyst actually produces.

// Power BI measures — what real analysts write
// A small set of DAX measures powers most dashboards.

Total Revenue = SUM ( Orders[amount] )

Total Orders = COUNTROWS ( Orders )

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

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

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

Active Customers =
DISTINCTCOUNT ( Orders[customer_id] )

// Why these measures matter:
// - They are the exact KPIs most business dashboards track.
// - They are reusable across multiple reports.
// - They demonstrate that you understand business metrics, not just visuals.
//
// What interviewers look for:
// - Do you know CALCULATE and time intelligence?
// - Can you explain what each KPI means in business terms?
// - Do you handle division by zero safely (DIVIDE)?