Dawn Choo

If I were to start my Data Analyst career from scratch, here is the roadmap I would follow:

𝟭/ 𝗟𝗲𝗮𝗿𝗻 𝗮𝗯𝗼𝘂𝘁 𝗥𝗲𝗹𝗮𝘁𝗶𝗼𝗻𝗮𝗹 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲𝘀

Understand basic database concepts
- Definition and purpose of databases

Master data table fundamentals
- Rows, columns, and primary keys
- Common data types

Grasp the concept of relationships between tables
- One-to-one, one-to-many, and many-to-many relationships
- Foreign keys and table connections

𝟮/ 𝗟𝗲𝗮𝗿𝗻 𝗘𝘅𝗰𝗲𝗹

Start with the basics
- Workbooks, worksheets, cells
- Common data formats
- Sorting and filtering data

Learn formulas and functions
- Common functions (SUM, AVERAGE, COUNT)
- Complex functions (VLOOKUP, IF statements)
- User-defined functions

Create & customize charts
- Chart types and their use cases
- Customizing chart components
- Conditional formatting for trends

Master pivot tables for data analysis
- Summarizing large datasets
- Interpreting and presenting data insights

𝟯/ 𝗟𝗲𝗮𝗿𝗻 𝗕𝗮𝘀𝗶𝗰 𝗦𝗤𝗟

Start with the common commands
- SELECT, FROM, WHERE
- ORDER BY, LIMIT, DISTINCT

Learn aggregations
- Functions: SUM, COUNT, AVG, MIN, MAX
- GROUP BY and HAVING clauses

Master JOINs
- Types: INNER, LEFT, RIGHT, FULL OUTER, CROSS
- When to use each type

Learn CTEs and sub-queries
- CTEs using WITH clause
- Sub-queries for data pre-processing

𝟰/ 𝗟𝗲𝗮𝗿𝗻 𝗮 𝗗𝗮𝘁𝗮 𝗩𝗶𝘀𝘂𝗮𝗹𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝘁𝗼𝗼𝗹

Foundations of data visualization
- Chart types and their applications
- Data types and variables

Learn data sources and preparation
- Connecting to various sources
- Cleaning and structuring data

Master creating charts and dashboards
- Common and complex chart creation
- Interactive visualizations with filters
- Combining charts into dashboards

𝟱/ 𝗗𝗲𝘃𝗲𝗹𝗼𝗽 𝗯𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝘀𝗲𝗻𝘀𝗲 𝗳𝗼𝗿 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀

Understand business fundamentals
- Basic concepts: revenue, costs, profit
- Key metrics across target industries

Learn how businesses use data for decisions
- Case studies on data-informed strategy
- Translating business questions to data queries

Develop communication skills
- Presenting technical findings to non-technical audiences
- Creating executive summaries
- Storytelling with data

Apply analytics to business problems
- Portfolio projects from various industries
- Gaining experience through internships or projects

𝟲/ 𝗕𝗼𝗻𝘂𝘀 𝗽𝗼𝗶𝗻𝘁𝘀: 𝗨𝗽𝘀𝗸𝗶𝗹𝗹 𝘁𝗼 𝗮𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟

Dive into window functions
- Definition and usage
- Construction and aggregation commands
- Functions: RANK(), LAG(), NTILE()

Learn how to optimize SQL queries
- Effective use of indexes
- Writing efficient subqueries and joins

Understand basics of Extract, Transform and Load processes
- ETL process overview
- Loading strategies and pipeline scheduling

------

♻️ Did you find this useful? If so, repost it so others can see it too.

👋🏼 I post about the Data career every day. Follow me for more!

I was a Data Analyst at Amazon for 2 years.

If I were to start my Analytics career again, I would study for these 5 interviews.

(𝘈𝘯𝘴𝘸𝘦𝘳𝘴 𝘵𝘰 𝘦𝘹𝘢𝘮𝘱𝘭𝘦 𝘲𝘶𝘦𝘴𝘵𝘪𝘰𝘯𝘴 𝘢𝘳𝘦 𝘪𝘯 𝘵𝘩𝘦 𝘥𝘰𝘤!)


𝟭/ 𝗦𝗤𝗟 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄

I would get comfortable with the basics of SQL. Then move to advanced concepts (e.g window functions, optimization techniques).

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
The 𝘥𝘢𝘪𝘭𝘺_𝘴𝘢𝘭𝘦𝘴 table has the columns 𝘥𝘢𝘵𝘦 and 𝘥𝘢𝘪𝘭𝘺_𝘴𝘢𝘭𝘦𝘴. How would you calculate 7-day moving average of daily sales?

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
Write a SQL query to find the top 5 customers by total purchase amount. The 𝘰𝘳𝘥𝘦𝘳𝘴 table has columns 𝘰𝘳𝘥𝘦𝘳_𝘪𝘥, 𝘤𝘶𝘴𝘵𝘰𝘮𝘦𝘳_𝘪𝘥, 𝘱𝘶𝘳𝘤𝘩𝘢𝘴𝘦_𝘢𝘮𝘰𝘶𝘯𝘵, 𝘥𝘢𝘵𝘦.


𝟮/ 𝗗𝗮𝘁𝗮 𝘃𝗶𝘀𝘂𝗮𝗹𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄

I would study data viz best practices and put that into a cheat sheet. If I got stuck in an interview, I would reference my cheat sheet.

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
You have a dataset of customer demographics and purchase history. How would you visualize customer segmentation?

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
You have daily data on a social media platform's user engagement. How would you visualize trends and patterns in user activity?


𝟯/ 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝗮𝗹 𝗰𝗮𝘀𝗲 𝘀𝘁𝘂𝗱𝗶𝗲𝘀

Learn analytics techniques and how to apply them to business problems. I would aim to do hands-on projects at my current job.

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
A e-commerce company is experiencing a decline in customer retention. How would you analyze this problem?

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
Our company's website traffic has plateaued. How would you investigate the causes and suggest improvements?


𝟰/ 𝗕𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝗰𝗮𝘀𝗲 𝘀𝘁𝘂𝗱𝗶𝗲𝘀

I would learn about how businesses use data to craft strategy. Then build projects to apply those learnings.

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
A coffee chain wants to increase revenue. Can you suggest strategies to do so?

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
Estimate the market size for electric scooters in New York City.


𝟱/ 𝗕𝗲𝗵𝗮𝘃𝗶𝗼𝗿𝗮𝗹 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝘀

I would practice telling 5-10 stories using the STAR method.

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
Describe a time when you had to explain complex data to non-technical stakeholders.

→ 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻:
Tell me about a time when you faced a challenge with data quality. How did you handle it?


———

PS: Level-up your SQL skills on Interview Master. We have 200+ FREE SQL practice questions on companies like Amazon, Google, Meta and more → www.InterviewMaster.ai

———

Hope you found this useful. If so, please repost ♻️

👋🏼 I write about Data every day. Follow me for more!

The BEST (& free) YouTube videos to learn Data Science.

(Crash courses, project tutorials & interview prep etc).


1️⃣ 𝗦𝗤𝗟

L͟e͟a͟r͟n͟ ͟S͟Q͟L͟

- 4-hour SQL crash course: https://lnkd.in/gRBYWq-J
- SQL window functions: https://lnkd.in/gRBYWq-J

B͟u͟i͟l͟d͟ ͟S͟Q͟L͟ ͟p͟r͟o͟j͟e͟c͟t͟s͟

- Set up for your first project: https://lnkd.in/gGq-hCst
- Data cleaning project: https://lnkd.in/gD8NgkDM
- Restaurant analysis: https://lnkd.in/gBkSjHZY

P͟r͟a͟c͟t͟i͟c͟e͟ ͟f͟o͟r͟ ͟S͟Q͟L͟ ͟i͟n͟t͟e͟r͟v͟i͟e͟w͟s͟

- How to ace SQL interviews: https://lnkd.in/gAhYW-Zf


2️⃣ 𝗣𝘆𝘁𝗵𝗼𝗻

L͟e͟a͟r͟n͟ ͟P͟y͟t͟h͟o͟n͟ ͟f͟o͟r͟ ͟D͟a͟t͟a͟ ͟S͟c͟i͟e͟n͟c͟e͟

- 12-hour course: https://lnkd.in/gvzGtCDs

B͟u͟i͟l͟d͟ ͟P͟y͟t͟h͟o͟n͟ ͟p͟r͟o͟j͟e͟c͟t͟s͟

↳ Beginner Python project: https://lnkd.in/g8V_zcpk
↳ Covid-19 Data Analysis: https://lnkd.in/gdaKdDer

P͟r͟a͟c͟t͟i͟c͟e͟ ͟f͟o͟r͟ ͟P͟y͟t͟h͟o͟n͟ ͟i͟n͟t͟e͟r͟v͟i͟e͟w͟s͟

- Python interview tips: https://lnkd.in/gwNcVf77
- Python interview questions: https://lnkd.in/gMyE-Vkv


3️⃣ 𝗦𝘁𝗮𝘁𝗶𝘀𝘁𝗶𝗰𝘀 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴

L͟e͟a͟r͟n͟ ͟S͟t͟a͟t͟i͟s͟t͟i͟c͟s͟ ͟&͟ ͟M͟a͟c͟h͟i͟n͟e͟ ͟L͟e͟a͟r͟n͟i͟n͟g͟

- 7-hour intro to statistics: https://lnkd.in/gZWMiYsU
- Learn ML playlist: https://lnkd.in/gu8Yizns

B͟u͟i͟l͟d͟ ͟M͟L͟ ͟p͟r͟o͟j͟e͟c͟t͟s͟

- Machine Learning project: https://lnkd.in/gt3pVfPN

P͟r͟a͟c͟t͟i͟c͟e͟ ͟f͟o͟r͟ ͟S͟t͟a͟t͟s͟ ͟&͟ ͟M͟L͟ ͟i͟n͟t͟e͟r͟v͟i͟e͟w͟s͟

- ML Interviews Q&A: https://lnkd.in/gt3Y5zvU
- Ace the Statistics interview: https://lnkd.in/gJ8t6_uG


4️⃣ 𝗣𝗿𝗼𝗱𝘂𝗰𝘁 & 𝗕𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝗖𝗮𝘀𝗲 𝗦𝘁𝘂𝗱𝗶𝗲𝘀

L͟e͟a͟r͟n͟ ͟p͟r͟o͟d͟u͟c͟t͟ ͟&͟ ͟b͟u͟s͟i͟n͟e͟s͟s͟ ͟s͟e͟n͟s͟e͟:͟
- Build strong product sense https://lnkd.in/gM2wCCy5
- Define product metrics: https://lnkd.in/g8XB8MF2

P͟r͟a͟c͟t͟i͟c͟e͟ ͟f͟o͟r͟ ͟c͟a͟s͟e͟ ͟s͟t͟u͟d͟y͟ ͟i͟n͟t͟e͟r͟v͟i͟e͟w͟
- Case interview framework: https://lnkd.in/gXvBujfC
- Ace the case interview: https://lnkd.in/g7ihbrJv

———

Found this useful? ♻️ Repost it please!

Stop doing guided projects. Start doing your OWN self-directed projects.

Here are 5 interesting Data project ideas and datasets to get you started:


→ 𝗧𝗼𝗽𝗶𝗰 𝟭: 𝗡𝗮𝘁𝗶𝗼𝗻𝗮𝗹 𝗙𝗼𝗼𝘁𝗯𝗮𝗹𝗹 𝗟𝗲𝗮𝗴𝘂𝗲 𝗣𝗹𝗮𝘆𝘀

𝗣𝗿𝗼𝗷𝗲𝗰𝘁 𝗶𝗱𝗲𝗮: Develop a predictive model for play outcomes based on game situation, player involvement, and historical performance metrics.

𝗦𝗸𝗶𝗹𝗹𝘀: Machine Learning, Time Series Analysis, Feature Engineering, Python

𝗗𝗮𝘁𝗮𝘀𝗲𝘁: https://lnkd.in/ghvayXs9


→ 𝗧𝗼𝗽𝗶𝗰 𝟮: 𝗙𝗮𝘀𝘁 𝗙𝗼𝗼𝗱 𝗡𝘂𝘁𝗿𝗶𝘁𝗶𝗼𝗻

𝗣𝗿𝗼𝗷𝗲𝗰𝘁 𝗶𝗱𝗲𝗮: Conduct a clustering analysis to group similar fast food items based on their nutritional profiles, potentially uncovering hidden patterns in menu offerings.

𝗦𝗸𝗶𝗹𝗹𝘀: Exploratory Data Analysis, Unsupervised Machine Learning, Python

𝗗𝗮𝘁𝗮𝘀𝗲𝘁: https://lnkd.in/gzesTx6A


→ 𝗧𝗼𝗽𝗶𝗰 𝟯: 𝗔𝗶𝗿𝗯𝗻𝗯 𝗹𝗶𝘀𝘁𝗶𝗻𝗴𝘀 𝗮𝗻𝗱 𝗿𝗲𝘃𝗶𝗲𝘄𝘀

𝗣𝗿𝗼𝗷𝗲𝗰𝘁 𝗶𝗱𝗲𝗮: Create a recommendation system for Airbnb users based on listing features, user preferences, and review scores.

𝗦𝗸𝗶𝗹𝗹𝘀: Machine Learning, Feature Engineering, SQL, Python

𝗗𝗮𝘁𝗮𝘀𝗲𝘁: https://lnkd.in/gS43Gnef


→ 𝗧𝗼𝗽𝗶𝗰 𝟰: 𝗠𝗼𝘃𝗶𝗲𝘀

𝗣𝗿𝗼𝗷𝗲𝗰𝘁 𝗶𝗱𝗲𝗮: Develop a movie recommendation system using collaborative filtering based on user ratings and movie features.

𝗦𝗸𝗶𝗹𝗹𝘀: Unsupervised Machine Learning, Feature Engineering, Python, SQL

𝗗𝗮𝘁𝗮𝘀𝗲𝘁: https://lnkd.in/g97JxVdg


→ 𝗧𝗼𝗽𝗶𝗰 𝟱: 𝗠𝗲𝗻𝘁𝗮𝗹 𝗵𝗲𝗮𝗹𝘁𝗵

𝗣𝗿𝗼𝗷𝗲𝗰𝘁 𝗶𝗱𝗲𝗮: Analyze global trends in mental health disorders and create interactive visualizations to showcase prevalence changes over time.

𝗦𝗸𝗶𝗹𝗹𝘀: Time Series Analysis, Data Visualization, Exploratory Data Analysis, Python

𝗗𝗮𝘁𝗮𝘀𝗲𝘁: https://lnkd.in/gcyE-85A


ICYMI, I have a completely free GitHub repo on how to build out your data portfolio -- 𝘨𝘪𝘷𝘦 𝘮𝘦 𝘢 𝘴𝘵𝘢𝘳 𝘱𝘭𝘦𝘢𝘴𝘦 :)  https://lnkd.in/gK69GwGv


♻️ Feeling inspired? Repost this so others can see it too