Analytics from Scratch

How it works

git clone https://github.com/Ani19arya/analytics-from-scratch
cd analytics-from-scratch/phases/01-sql/01-group-by-from-raw-rows
python3 test_lesson.py

Phase 01: SQL from raw tables

1. 1. GROUP BY from raw rows

   How much did each city bring in last quarter, and which cities are big enough to matter?

2. 2. Joins without losing rows

   Which customers have never ordered, and what is revenue by city without counting any order twice?

3. 3. Window functions

   How is each region tracking against its year to date, who are the top reps, and did revenue go up or down from last month?

4. 4. Cohort retention

   Of the users who signed up in each month, what share were still active one, two and three months later?

Phase 02: Statistics and experiments

1. 1. Mean, median and skew

   What is the typical posted pay for an analyst role, and which number should we put on the slide?

2. 2. Confidence interval by hand

   How sure are we about an average or a rate measured on a sample?

3. 3. A/B test from scratch

   Did the new checkout page really convert better, or did we get lucky?

4. 4. Sample size before you test

   How many users, and how many weeks, do we need before an A/B test can answer the question we care about?

Phase 03: Metrics and product analytics

1. 1. Metric trees

   Revenue fell this week, so which part of the business made it fall?

2. 2. Funnel conversion

   Of the people who look at a course, how many buy it, and at which step do we lose the most of them?

3. 3. Active users and stickiness

   How many people really use the app, and how often do they come back?

4. 4. North star and guardrails

   The new feature moved our main metric up, so should we launch it?

Phase 04: Dashboards (Power BI and Tableau)

1. 1. Model the data a dashboard needs

   How do you turn one flat export of order lines into tables a dashboard can slice by date, product and region without double counting?

2. 2. DAX measures from scratch

   What does a Power BI measure actually compute, and how do you get the same Total Sales, filtered sales, year to date and growth on last year numbers without Power BI?

3. 3. Tableau LOD expressions in SQL

   How do you compute a number at a different level of detail from the chart it sits in, such as each customer's first order month in a chart by month, or a region's total in a chart by customer?

4. 4. Draw a chart from scratch

   What does a dashboard tool do when it turns a table into a bar chart, and which chart should you pick in the first place?

Phase 05: Python for analysts

1. 1. Clean a messy CSV

   Which rows of this export can we trust, and what did we change to get there?

2. 2. Reshape wide and long

   The data has one row per region and month. The manager wants one row per region. How do you move between the two without losing anything?

3. 3. Dates, weeks and time zones

   How many people signed up each week over the new year, counted on the clock the team actually works on?

4. 4. Automate the weekly report

   Can the Monday KPI report write itself, with the same numbers every time it runs?

Phase 06: AI for analysts

1. 1. Grade an LLM classifier

   Is the LLM good enough at spotting refund tickets that we can route them automatically?

2. 2. Check LLM-written SQL

   Which of the queries the LLM wrote can we trust, and which ones would have put a wrong number in the report?

3. 3. Catch made-up numbers

   Every number in the AI-written summary: is it actually in the data?

Phase 07: Getting hired

1. 1. Read job postings like data

   Across 300 analyst job postings, which tools are asked for most, how much experience do they want, and what do they say about visa sponsorship?

2. 2. SQL interview questions

   Can you answer the six SQL questions that come up again and again in analyst interviews, and explain why the obvious answer is wrong?

3. 3. Portfolio project end to end

   Do customers who start on a free trial cancel within 90 days more often than customers who pay from day one?

4. 4. Match your resume to a posting

   Of five open roles, which ones fit this resume best, and which missing skills stand between the candidate and the rest?

Learn with your coding agent