Portfolio project end to end

The problem

Pantrybox, a fictional meal kit subscription, offers a 14 day free trial. Joanna, the head of growth, thinks trial customers leave faster and wants to know before the 2027 budget review on 6 November. You have one table of 1,400 customers, as of 1 October 2026. You will do the whole case the way a portfolio project should look: a clear question, a SQL pull, a churn comparison with a significance test you write yourself, and a one page write-up that the script generates. This is the shape a hiring manager wants to see: a question someone cares about, an honest method, and a recommendation with its limits.

The file data/subscriptions.csv is synthetic, generated by make_data.py with a fixed seed. Pantrybox and its customers are invented.

What you will build from scratch

• CHURN_SQL: customers and 90 day cancellations by start type, for customers with a full 90 days of history.
• two_proportion_test: a z test and a 95% confidence interval for the gap between two rates.
• write_up: a markdown page with Question, Data, Method, Result, Recommendation and Limitations.

The idea

Define churn so everyone has the same chance. "Cancelled within 90 days of signup" is only fair for customers who signed up at least 90 days before the data date. A customer who joined last week has not had time to cancel, and counting them as retained makes churn look lower. So the query keeps signups up to 90 days before 1 October 2026.

Compare two proportions. Trial churn is p1 = x1 / n1, paid churn is p2 = x2 / n2. Under the null hypothesis that both groups churn at the same rate, the best estimate of that shared rate is the pooled rate:

p_pool = (x1 + x2) / (n1 + n2)
se_pool = sqrt(p_pool * (1 - p_pool) * (1/n1 + 1/n2))
z = (p1 - p2) / se_pool

For a two sided test, the p value is the chance of a z at least this far from 0 in either direction under a standard normal: p = erfc(|z| / sqrt(2)). The math module has erfc, so no stats library is needed.

For the confidence interval you do not assume the rates are equal, so you use the unpooled standard error:

se = sqrt(p1 * (1 - p1) / n1 + p2 * (1 - p2) / n2)
95% CI = (p1 - p2) +/- 1.96 * se

Write for the reader. A hiring manager reads the question, skips to the result, and then looks at the limitations to see whether you understand what the numbers cannot say. Put the counts next to the rates, so the reader can see the sample size.

Walk through

Run these from the lesson folder.

Step 1. The trap of customers who have not had 90 days yet.

import sys
sys.path.insert(0, "solution")
import solution as s

con = s.load_db()
q = """SELECT start_type, COUNT(*),
       SUM(cancel_date IS NOT NULL AND julianday(cancel_date) - julianday(signup_date) <= 90)
       FROM subscriptions GROUP BY start_type ORDER BY start_type"""
print("everyone:", con.execute(q).fetchall())
print("90 days of history:", s.pull(con))

With everyone, trial churn is 210 of 777 (27.0%) and paid is 101 of 623 (16.2%). With only customers who had 90 days, it is 171 of 578 (29.6%) and 87 of 485 (17.9%). Both rates were too low, because 337 recent customers were counted as staying when their outcome is not known.

Step 2. The test.

import sys
sys.path.insert(0, "solution")
import solution as s

g = s.pull(s.load_db())
t = s.two_proportion_test(g["trial"][1], g["trial"][0], g["paid"][1], g["paid"][0])
print({k: round(v, 4) for k, v in t.items()})

The gap is 11.6 percentage points, z is 4.41, and the 95% interval for the gap is 6.6 to 16.7 points. The p value is below 0.0001. The interval is more useful than the p value: it says the gap is at least about 7 points, which is large for a churn rate.

Step 3. A check a careful analyst adds. Plan also drives churn, so compare within each plan.

import sys
sys.path.insert(0, "solution")
import solution as s

con = s.load_db()
q = """SELECT plan, start_type, COUNT(*),
       SUM(cancel_date IS NOT NULL AND julianday(cancel_date) - julianday(signup_date) <= 90)
       FROM subscriptions WHERE julianday('2026-10-01') - julianday(signup_date) >= 90
       GROUP BY plan, start_type ORDER BY plan, start_type"""
for row in con.execute(q):
    print(row)

Monthly: trial 159 of 461 (34.5%), paid 83 of 386 (21.5%). Annual: trial 12 of 117 (10.3%), paid 4 of 99 (4.0%). The gap holds inside both plans, so it is not explained by plan mix. The annual groups are small, so their rates are less certain.

Step 4. Generate the page.

import sys
sys.path.insert(0, "solution")
import solution as s

s.main()

Save it with python3 solution/solution.py > writeup.md and put it at the top of your project's repository. The recommendation does not say "remove the trial". The data shows trial starters churn more, not that the trial causes it: people who choose a free trial may be less committed to begin with. The honest next step is an experiment.

Your turn

Open exercise.py. Write CHURN_SQL, then fill in two_proportion_test and write_up. Then run, from this folder:

python3 test_lesson.py

To see the reference solution pass: CHECK_SOLUTION=1 python3 test_lesson.py.

Then make it yours: pick a different question for the same table (annual against monthly, or churn by signup month), change the query and the write-up, and keep all six sections. When you describe the project on your resume, the resume bullet builder helps you turn it into one line with a number.

Check yourself

1. Why leave out customers who signed up in the last 90 days?
2. Why use the pooled rate for the z test but not for the confidence interval?
3. The p value is tiny. Can you tell Joanna the trial causes churn?
4. A hiring manager has two minutes. Which two sections do they read?

In real job postings

In the Real Analyst Jobs live feed on 25 October 2026, 13% of the 2,848 US analyst postings are entry level, so most junior candidates compete for a small share of roles, and a finished project with a clear write-up is evidence you can do the job. The SQL case study and how to become a data analyst pages have more on building one.

Next

04: Match your resume to a posting