Turn projects into interviews
How to write each project as a CV line, what entry-level analyst interviews ask, and how to answer in plain English.
The one-page analyst CV
Recruiters skim. Put the proof first: what you built, with numbers.
- Header: name, city, email, LinkedIn, portfolio or GitHub link.
- Summary: two lines. What you do and the tools you use.
- Skills: grouped (Data and BI, Analysis, Tools), matching the words in the job ad.
- Projects: three or four, each with two or three bullets.
- Experience: internships and part-time work count. Pull out anything with data in it.
- Education and certifications.
UK and India differences
- UK: no photo, date of birth or marital status. One page is fine for freshers, two at most. Include your degree result (for example 2:1).
- India: one page is common for freshers. Include your CGPA if it helps you. A photo is optional.
- Both: simple layout, standard headings, saved as PDF unless the ad asks for Word. Fancy columns and graphics can confuse application tracking systems.
The first two strong lines are real bullets from my own CV. Every series project ends with a ready-made CV line like these.
SQL questions you will get
These come up in most entry-level analyst interviews and take-home tests. Say the idea first, then write the query.
Practise them on real data in weeks 1 to 4 of the series.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation. Use WHERE for row conditions (status = 'paid') and HAVING for conditions on totals (SUM(amount) > 10000).
SELECT city, SUM(amount) AS revenue FROM orders WHERE status = 'paid' -- rows GROUP BY city HAVING SUM(amount) > 10000; -- groups
INNER JOIN vs LEFT JOIN?
INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table, with NULLs where the right table has no match.
Classic follow-up: "find customers who never ordered" is a LEFT JOIN where the order side IS NULL.
How do you find duplicate rows?
Group by the columns that should be unique and keep groups with more than one row.
SELECT email, COUNT(*) AS n FROM customers GROUP BY email HAVING COUNT(*) > 1;
Find the second-highest value (for example, salary).
A window function handles ties cleanly. DENSE_RANK gives equal values the same rank with no gaps.
SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM employees ) t WHERE rk = 2;
ROW_NUMBER vs RANK vs DENSE_RANK?
With two people tied for first: ROW_NUMBER gives 1, 2, 3 (no ties). RANK gives 1, 1, 3 (skips). DENSE_RANK gives 1, 1, 2 (no gaps).
COUNT(*) vs COUNT(column)?
COUNT(*) counts rows. COUNT(column) skips NULLs in that column. COUNT(DISTINCT column) counts unique non-null values. Interviewers use this to check you think about missing data.
What is a CTE and why use one?
A CTE (WITH ... AS) is a named temporary result you can query like a table. It makes long queries readable step by step, and you can reuse the same step more than once in the query.
Excel, stats, BI and case questions
Interviewers care less about definitions than about whether you can explain an idea to someone who doesn't use data.
Questions to ask them
- What does a good first three months look like in this role?
- Which tools and data sources would I use day to day?
- Who uses the analysis, and how do they make decisions with it?
Excel: XLOOKUP vs VLOOKUP?
XLOOKUP can look left or right, defaults to an exact match, and has a built-in "if not found" value. VLOOKUP only looks to the right and breaks if columns are inserted. Mention INDEX/MATCH if the company uses older Excel.
Excel: when would you use a pivot table?
To summarise a large table fast: totals by month, region or product, without writing formulas. It is the spreadsheet version of GROUP BY.
Stats: mean or median?
Use the median when the data is skewed or has outliers (salaries, order values, house prices). The mean gets pulled by a few huge values. Say which you used and why.
Stats: explain a p-value in plain English.
Roughly: if there were really no difference, how surprising would a result like ours be? A small p-value (often below 0.05) means "this would be unusual by chance alone". It does not tell you how big or important the effect is.
Stats: correlation vs causation?
Correlation means two things move together. Causation means one makes the other happen. Ice cream sales and sunburn rise together because of the weather, not because of each other. A controlled experiment like an A/B test is the usual way to test cause.
Power BI: calculated column vs measure?
A calculated column is worked out row by row and stored in the table. A measure is calculated on the fly for whatever filters are on the report (total revenue for the selected month). Most totals and ratios should be measures.
Case: sales dropped 10% last month. How would you investigate?
- Check the data first. Did tracking, a definition or a filter change?
- Find when it started. A sudden drop and a slow slide have different causes.
- Split it. By region, product, channel, and new vs returning customers. Usually one segment explains most of it.
- Look outside. Seasonality, a price change, a competitor, a holiday.
- Recommend. What you would check or change next, and how you would measure it.
Behavioural: tell me about a project.
Use 60 seconds and this order: the question, the data (how big, how messy), what you did (two or three steps, tools named), the result (a number), and what you would do next. Practise it out loud until it fits.
Before you apply
Portfolio link
One link on your CV that opens all your projects. A GitHub profile with a pinned repository per project works.
A README per project
The question, the data source, what you did, the result with a chart, and how to run it. Five short sections.
LinkedIn headline
Say what you do and with what: "Aspiring Data Analyst · SQL, Excel, Power BI · 4 portfolio projects".
Tailor each CV
Copy the tools and duties from the job ad into your skills and bullets, where they are true for you.
Your 60-second pitch
One project, told as question, data, method, result, next step. Practise it out loud.
Apply steadily
A few tailored applications a day beats a hundred copies in one night. Track them in a simple sheet.