All roadmaps

Data analyst

Data analyst path

Query data with SQL, then clean and summarise it with Python and Pandas, and finish with real business questions.

  • about 14 weeks at an hour a day
  • 6 stages, 54 steps
Start step 1See the roadmap

Read and summarise tables with SQL

about 3 weeks · 13 steps

After this stage: You can answer 'how many' and 'how much' questions with a query.

  1. SQL: tables, rows and columnsLessonFreeHow data is laid out.01
  2. SQL NULL handlingLessonLockedMissing values change every result.02
  3. SQL aggregations and groupingLessonLockedTotals and averages per group.03
  4. SQL multi-table joinsLessonLockedBring related tables together.04
  5. SQL CASE expressionsLessonLockedLabel and bucket values in a query.05
  6. SQL window functionsLessonLockedRankings and running totals.06
  7. SQL 50 practice sheetPracticePractise the queries.07
  8. Subqueries and CTEsLessonLockedBreak a business question into readable steps.08
  9. Cleaning text and dates in SQLLessonLockedFix messy values before you count them.09
  10. Set operationsLessonLockedCombine and compare result sets.10
  11. Pivoting and unpivotingLessonLockedTurn rows into the columns a report needs.11
  12. Advanced aggregationsLessonLockedSubtotals and grouping sets.12
  13. Advanced analytics patternsLessonLockedFunnels, retention and gaps.13

Clean and reshape with Pandas

about 3 weeks · 18 steps

After this stage: You can load a messy file, fix it and summarise it in Python.

  1. Your first Python programLessonFreeEnough Python to drive Pandas.14
  2. What is pandas?LessonFreeTables in Python.15
  3. Reading CSV, Excel and JSONLessonFreeGet data in.16
  4. Cleaning and type castingLessonLockedFix the data before you analyse it.17
  5. Filtering and slicingLessonLockedPick the rows and columns you need.18
  6. Grouping and aggregationsLessonLockedThe Pandas twin of GROUP BY.19
  7. Pandas versus SQLLessonLockedThe same question in both tools.20
  8. Pandas 30 practice sheetPracticePractise the table work.21
  9. Variables, types and type hintsLessonLockedThe Python you need before pandas.22
  10. Lists, dictionaries and setsLessonLockedContainers you will use around every DataFrame.23
  11. Series and DataFramesLessonFreeThe two objects everything is built on.24
  12. apply and mapLessonLockedRun your own logic on a column.25
  13. Transformations and derived columnsLessonLockedAdd the columns your analysis needs.26
  14. Combining datasetsLessonLockedmerge, join and concat.27
  15. Reshaping and pivot tablesLessonLockedWide to long and back.28
  16. Time series in pandasLessonLockedDates, resampling and rolling windows.29
  17. The .str accessorLessonLockedClean text columns quickly.30
  18. Advanced groupbyLessonLockedtransform, filter and custom aggregates.31

Think and check like an analyst

about 2 weeks · 9 steps

After this stage: You can pin down a vague question, check that your numbers hold up and compare groups fairly.

  1. From a business question to a table questionLessonFreeDecide the metric, rows and dates first.32
  2. Your first look at a datasetLessonFreeFive commands before any analysis.33
  3. Do two sources agree?LessonLockedReconcile before you report a total.34
  4. Average, median and why they disagreeLessonLockedPick the right average.35
  5. Rates, not countsLessonLockedCompare groups of different sizes fairly.36
  6. Reading a trend without being fooledLessonLockedPartial weeks look like crashes.37
  7. Outliers: keep, cap or remove?LessonLockedDecide without hiding real signal.38
  8. Capstone: write the findingLessonLockedTurn a result into a clear statement.39
  9. Capstone: messy data to analysis-readyLessonLockedClean a real mess in pandas.40

Understand warehouse data

about 2 weeks · 4 steps

After this stage: You can read a star schema and pick the right table and measure.

  1. OLTP vs OLAP mental modelLessonLockedWhy reporting data looks different from app data.41
  2. Star schema: facts, dimensions, grainLessonLockedFind the table and the grain before you query.42
  3. Additive, semi-additive and non-additive factsLessonLockedKnow which numbers you can safely sum.43
  4. Views vs materialized viewsLessonLockedHow dashboards stay fast.44

Business questions end to end

about 3 weeks · 7 steps

After this stage: You can ask the next question, check the data and give a recommendation.

  1. Case study: where does the revenue come from?Case studyDecide what counts, then look by week and by customer.45
  2. Case study: which payment method loses money?Case studyCheck grain, compare rates and size the loss.46
  3. Case study: where do shoppers drop off?Case studyA funnel, step by step.47
  4. Case study: do new members come back?Case studyRetention for a subscription app.48
  5. Case study: which ad deserves the credit?Case studyAttribution in marketing data.49
  6. Case study: why do rides get cancelled?Case studyFind the pattern behind a problem.50
  7. Analytics projectsProjectScorecards, experiments and RFM segmentation.51

Get hired

about 1 week · 3 steps

After this stage: You have proof you can do the work and answers ready for the interview.

  1. SQL questions for freshersInterviewThe questions analysts are asked first.52
  2. Mock interviewToolPractise out loud and get scored.53
  3. SQL certificate examToolA timed exam that gives you a link to share.54

After this roadmap