Data Analyst Field Guide

Analysts turn messy business data into decisions. Every tool below serves one step of this loop.

• Loop: question → collect → clean → transform → analyse → visualise → insight → decision → measure again.
• Four kinds of analytics: descriptive (what happened), diagnostic (why), predictive (what will happen), prescriptive (what to do).
• Diagnosing a drop: check the data first (missing loads, duplicates), then slice one dimension at a time: time, region, product, channel, price, customer segment.
• Roles: analyst answers business questions; data scientist builds models; data engineer builds pipelines.
• Most of the job is cleaning: inconsistent categories ("Blr" vs "Bengaluru"), duplicates, blanks, wrong types.

• Tidy data: one row per record, one column per attribute, no merged cells or subtotals inside data.
• Convert ranges to Tables (Ctrl+T): they auto-expand and give readable structured references.
• Sort, filter (AND/OR), Freeze Panes, Find & Replace, Data Validation dropdowns.
• Types matter: numbers stored as text silently break SUM. Formatting changes the look, not the value.
• SUM AVERAGE MIN MAX; COUNT (numbers) vs COUNTA (non-empty) vs COUNTBLANK (quality checks); ROUND/ROUNDUP/ROUNDDOWN.
• References: relative A1, absolute $F$1, mixed A$1 / $A1. Put constants (tax rate) in a cell, never in the formula.

• IF → nested IF → IFS (first true wins; end with TRUE,"Other").
• AND() / OR() inside IF.
• SUMIF COUNTIF AVERAGEIF and multi-condition SUMIFS COUNTIFS AVERAGEIFS MAXIFS MINIFS.
• Criteria from a cell: =SUMIFS(C:C,B:B,"IT",C:C,">"&F2).

Part 5 was deleted from the channel. Lookups are the most-asked Excel interview topic.

• XLOOKUP (365/2021): =XLOOKUP(id, A:A, C:C, "Not found"). Looks left or right, exact match by default, built-in not-found value.
• VLOOKUP: =VLOOKUP(id, A:D, 3, FALSE). Always pass FALSE; it can't look left and breaks when columns are inserted.
• INDEX/MATCH: =INDEX(C:C, MATCH(id, A:A, 0)). Works in every version, flexible direction.
• Two-way lookup: =INDEX(B2:M20, MATCH(row,A2:A20,0), MATCH(col,B1:M1,0)).
• Multiple criteria: =XLOOKUP(1, (A:A=x)*(B:B=y), C:C).
• Approximate match for bands (tax slabs, grades): sorted table + XLOOKUP(..., -1) or VLOOKUP(..., TRUE).
• Wrap legacy lookups in IFERROR(...,""); check for trailing spaces when a match "should" work.

• TRIM, CLEAN, TRIM(CLEAN()); UPPER LOWER PROPER (watch acronyms).
• LEN to validate IDs; LEFT RIGHT MID to extract.
• FIND (case-sensitive) vs SEARCH; SUBSTITUTE.
• CONCAT, TEXTJOIN(", ",TRUE,…), TEXTSPLIT, TEXTBEFORE/TEXTAFTER (email → user, domain).
• Order: trim → standardise → extract → replace → combine → validate.

• Dates are serial numbers, so end − start gives days.
• TODAY NOW DATE YEAR MONTH DAY; EOMONTH(d,0) month end; EDATE(d,3) +3 months.
• DATEDIF(start,end,"Y") tenure; NETWORKDAYS / WORKDAY with holidays.
• TEXT(d,"mmm-yyyy") for labels. Overdue: =MAX(0,TODAY()-Due).
• Dates stored as text must be converted (DATEVALUE or Power Query) before any maths.

Part 8 was skipped on the channel. The roadmap lists dynamic arrays and Goal Seek here.

• Dynamic arrays spill results: UNIQUE, SORT, SORTBY, FILTER(A2:D100, C2:C100="IT"), SEQUENCE, TAKE/DROP, VSTACK/HSTACK.
• Top-N list: =TAKE(SORT(FILTER(A2:C100,B2:B100="IT"),3,-1),5).
• LET names intermediate results; LAMBDA makes reusable custom functions.
• Ranking and positions: RANK.EQ, LARGE/SMALL, AGGREGATE(14,6,…) ignores errors.
• Goal Seek (Data → What-If): find the input that hits a target. Scenario Manager and Data Tables compare several inputs at once.
• Conditional formatting: colour scales, data bars, formula rules like =$C2>AVERAGE($C:$C).

• Four areas: Rows, Columns, Values, Filters. Switch Sum/Count/Average; Show Values As → % of total.
• Group dates by month, quarter or year. Slicers, connected to several pivots. PivotCharts.
• Double-click a value to drill down to its rows. Refresh after the source changes.
• Build from a Table so new rows are picked up.

• ETL: Connect → Transform → Load. Every step is recorded, so Refresh redoes all the cleaning.
• Set types, remove duplicates (check they're real first), remove or rename columns, filter, replace, handle nulls, trim.
• Split and merge columns, custom and conditional columns, Group By.
• Merge = JOIN, Append = UNION. Unpivot turns a wide month-per-column layout into tidy rows.

• Shortcuts: Ctrl+arrows jump, Ctrl+Shift+arrows select, Ctrl+1 format, Ctrl+T table, Alt+= AutoSum, Ctrl+Shift+L filter.

• Tables, rows, columns, primary key (unique row ID), foreign key (link to another table), one-to-many.
• SELECT named columns (not *), WHERE, comparisons, AND/OR, IN, BETWEEN, LIKE 'A%', DISTINCT, ORDER BY, LIMIT (TOP in SQL Server), aliases.

• COUNT(*) counts rows; COUNT(col) skips NULLs; COUNT(DISTINCT col).
• Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
• WHERE filters rows before grouping; HAVING filters groups after.
• AOV = SUM(amount) / COUNT(DISTINCT order_id).

SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY region, month
HAVING SUM(amount) > 100000
ORDER BY revenue DESC;

• CASE WHEN … THEN … ELSE … END, evaluated top-down. Use it for tiers, flags and cleaning inconsistent values.
• Conditional aggregation: SUM(CASE WHEN region='North' THEN amount END), a pivot in SQL.
• NULL is not 0 and not ''. Use IS NULL, never = NULL.
• COALESCE(a,b,'Unknown') fallback; x / NULLIF(y,0) safe division.

• INNER (matches only), LEFT (all left rows), FULL OUTER, self join (employee → manager). Rewrite RIGHT as LEFT.
• Anti-join: LEFT JOIN orders o ON … WHERE o.id IS NULL gives customers with no orders.
• JOIN adds columns; UNION stacks rows.

Trap: joining a one-to-many table multiplies rows, so a later SUM double counts. Check the row count before and after every join.

• Scalar subquery (WHERE salary > (SELECT AVG(salary) …)), IN, EXISTS.
• NOT IN returns nothing if the list has a NULL, so prefer NOT EXISTS.
• CTE (WITH x AS (…)) breaks logic into readable steps. It's not automatically faster.
• Correlated subqueries run per row and can often be rewritten as a window function.

• Windows keep every row; GROUP BY collapses rows. Syntax: fn() OVER (PARTITION BY … ORDER BY …).
• Ties at 100, 100, 90: ROW_NUMBER gives 1,2,3, RANK gives 1,1,3, DENSE_RANK gives 1,1,2.
• LAG/LEAD previous and next row; running total SUM(x) OVER (ORDER BY d); 3-row moving average ROWS BETWEEN 2 PRECEDING AND CURRENT ROW; NTILE(4) quartiles.
• You can't filter a window in WHERE. Wrap it in a CTE first.

WITH r AS (
  SELECT dept, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
  FROM employees)
SELECT * FROM r WHERE rk <= 3;   -- top 3 per department

• EXTRACT(YEAR FROM d), DATE_TRUNC('month', d). Group by year and month, never month alone.
• Use half-open ranges: d >= '2026-01-01' AND d < '2027-01-01'. YEAR(d)=2026 in WHERE blocks index use.
• Last 30 days: d >= CURRENT_DATE - INTERVAL '30 days'. DATEDIFF syntax differs by database.
• MoM and YoY growth with LAG; first purchase with ROW_NUMBER; gap between purchases with LAG per customer; inactive if MAX(d) < CURRENT_DATE - 90.

• Decide the grain first (one row per order or per order item?).
• Recipe: filter → join → aggregate to grain → window → filter → present.
• Patterns: top-N per group, 2nd highest (DENSE_RANK()=2), never-sold products (anti-join), above-average customers, MoM % with NULLIF, latest order per customer (ROW_NUMBER … DESC, rn = 1), duplicates (rn > 1).

WITH m AS (SELECT DATE_TRUNC('month',order_date) mo, SUM(amount) rev FROM orders GROUP BY 1)
SELECT mo, rev,
       ROUND(100.0*(rev - LAG(rev) OVER (ORDER BY mo)) / NULLIF(LAG(rev) OVER (ORDER BY mo),0),1) AS mom_pct
FROM m;

• Select only needed columns, filter early, index join and filter keys. Indexes speed reads but slow writes.
• Keep filters sargable: no functions wrapped around the filtered column.
• Read EXPLAIN ANALYZE: scan vs seek, estimated vs actual rows. A full scan is fine on small tables.
• UNION ALL over UNION when duplicates can't occur. DISTINCT that hides join duplicates is a bug, not a fix.

• Cohort = month of first purchase (MIN(order_date)). Months since cohort = activity month − cohort month.
• Retention % = active customers ÷ cohort size, laid out as a cohort × month matrix.
• RFM: Recency (MAX(date)), Frequency (COUNT(DISTINCT order_id)), Monetary (SUM), then CASE into segments.
• Repeat customers: HAVING COUNT(DISTINCT order_id) > 1. Churn = inactivity past a business-defined threshold.
• Simple CLV ≈ AOV × purchase frequency × expected lifespan.

Part 21 is missing from the channel. These are the roadmap's remaining SQL topics.

• UNION (dedupes) / UNION ALL / INTERSECT (in both) / EXCEPT or MINUS (in first, not second). Column count and types must match.
• Recursive CTE walks hierarchies (org chart, category tree) or generates a date series:

WITH RECURSIVE days AS (
  SELECT DATE '2026-01-01' AS d
  UNION ALL SELECT d + 1 FROM days WHERE d < DATE '2026-01-31')
SELECT d FROM days;   -- join sales to this to show zero-sales days

• Views save a query under a name; temp tables hold intermediate results; materialised views store results for speed.
• Normalisation (1NF–3NF) removes repeated data in source systems. Analytics models denormalise into star schemas on purpose.
• STRING_AGG, COALESCE and CAST come up often in practical tasks.

• Approach: state the output → tables → filters → joins → grain → aggregates → HAVING → window → validate (duplicates, NULLs, row counts).
• Always-asked: WHERE vs HAVING, RANK vs DENSE_RANK, LEFT JOIN anti-join, top-N per group, 2nd highest, duplicates, running total, MoM growth, NULL behaviour, UNION vs UNION ALL, indexes.

On the roadmap but not posted yet. This is the level interviews expect.

Describe

• Mean (skewed by outliers), median (robust: use it for income, house prices, order values), mode.
• Spread: range, variance, standard deviation, IQR (Q3 − Q1). Outliers sit beyond Q1 − 1.5·IQR or Q3 + 1.5·IQR.
• Skew: right-skewed means mean > median. Percentiles: P90 latency, P50 delivery time.

Distributions and probability

• Normal: 68 / 95 / 99.7% within 1 / 2 / 3 SD. z-score = (x − mean) / SD.
• Binomial (yes/no counts such as conversions), Poisson (events per period such as tickets per hour).
• Central limit theorem: averages of samples are roughly normal, which is why confidence intervals work.

Inference and A/B testing

• H₀ = no difference. p-value = chance of a result this extreme if H₀ were true. p < 0.05 → reject H₀.
• 95% confidence interval ≈ estimate ± 1.96 × standard error.
• A/B test: one metric decided in advance, random split, sample size fixed in advance, run full weeks, don't stop at the first "significant" peek.
• Tests: t-test (compare means), chi-square (compare proportions or categories), correlation (Pearson r, from −1 to 1).

Trap: correlation isn't causation. Look for a confounder (ice-cream sales and drownings both rise with summer).

• Desktop builds the report, Service shares and refreshes it, Mobile views it.
• Flow: Get Data → Power Query → Model → DAX measures → Visuals → Publish.
• Filters work at visual, page and report level. Use slicers for user filtering.
• A report is pages built in Desktop; a dashboard is pinned tiles in the Service.
• Beginner mistakes: no date table, calculated columns everywhere, flat single-table model, too many visuals.

• Same engine as Excel. Merge vs Append, Applied Steps, Unpivot.
• Query folding: steps get pushed back to the source database as SQL. Keep foldable steps (filter, remove columns) first.
• Rule of thumb: Power Query shapes data, SQL queries at the source, DAX calculates in the model.

• Fact tables hold events and numbers (sales lines). Dimension tables hold descriptions (product, customer, date).
• Star schema: dimensions → one-to-many → fact, single filter direction. Snowflake means dimensions split further. Avoid it unless needed.
• Build a dedicated Date table and mark it. Sort Month Name by Month Number.
• Know each table's grain. Inactive relationships plus USERELATIONSHIP handle role-playing dates (order date vs ship date).
• Avoid bi-directional and many-to-many relationships unless you can explain why.

• Measures are computed live and respond to filters, so prefer them. Calculated columns are stored per row; use them only for slicing or categorising.
• SUM AVERAGE COUNTROWS DISTINCTCOUNT, DIVIDE(a,b), and measures reused inside measures.
• Filter context comes from slicers and visuals. Row context comes from iterators and columns. RELATED() reads across a relationship.
• CALCULATE(expr, filters…) rewrites the filter context: REMOVEFILTERS/ALL ignore filters, ALLSELECTED respects slicers, KEEPFILTERS intersects, FILTER() applies row conditions.
• Context transition: CALCULATE inside a row context turns the current row into a filter.
• Iterators: SUMX(Sales, Sales[Qty]*Sales[Price]), AVERAGEX, RANKX.

Total Sales  = SUM(Sales[Amount])
Sales YTD    = TOTALYTD([Total Sales], 'Date'[Date])
Sales LY     = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY %        = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
% of Total   = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)))
Product Rank = RANKX(ALL(Product[Name]), [Total Sales])

• Performance: Performance Analyzer, drop unused columns, aggregations, simpler DAX, fewer visuals per page. Live data: DirectQuery or scheduled refresh.

• Dashboard layout: KPIs top-left, trend next, breakdowns below, detail last. 5–7 visuals per page.
• Start bar axes at zero, label directly instead of using legends, use one accent colour to highlight the point, and title each chart with the takeaway ("Q3 sales fell 12% in South").

On the roadmap but not covered on the channel. This is the SQL-equivalent set in pandas.

import pandas as pd
df = pd.read_csv("orders.csv", parse_dates=["order_date"])
df.info(); df.describe(); df.isna().sum(); df.duplicated().sum()

df = df.drop_duplicates().dropna(subset=["customer_id"])
df["city"] = df["city"].str.strip().str.title()
df["amount"] = pd.to_numeric(df["amount"], errors="coerce").fillna(0)

df[(df.region == "South") & (df.amount > 1000)]                 # WHERE
df.groupby("region").agg(rev=("amount","sum"), orders=("order_id","nunique"))   # GROUP BY
df.merge(customers, on="customer_id", how="left")                 # LEFT JOIN
df.pivot_table(index="region", columns="month", values="amount", aggfunc="sum")
df.sort_values("amount", ascending=False).head(10)                # TOP 10
df["rank"] = df.groupby("dept")["salary"].rank(method="dense", ascending=False)
df["prev"] = df.sort_values("order_date").groupby("customer_id")["amount"].shift(1)   # LAG
monthly = df.set_index("order_date").resample("MS")["amount"].sum()
monthly.pct_change()                                              # MoM growth

• NumPy basics: arrays, vectorised maths, np.where (CASE WHEN).
• Charts: matplotlib / seaborn (line, bar, hist, box, heatmap of correlations).
• Work in Jupyter, keep raw data untouched, and save cleaning as code so it can be rerun.

• Every KPI needs a definition, a comparison (target, last period, LY) and an owner. A number without a comparison isn't an insight.

• OLTP app databases (Postgres, MySQL) record transactions. OLAP warehouses (BigQuery, Snowflake, Redshift, Synapse) run analysis.
• Warehouse = structured, modelled tables. Lake = raw files (S3, ADLS). Lakehouse = both (Databricks, Fabric).
• ETL transforms before load. ELT loads raw data and transforms inside the warehouse (dbt), which is the modern default.
• Pipelines: ingestion (Fivetran, Airbyte), orchestration (Airflow), transformation (dbt), BI on top.
• AI in the job: draft SQL, regex and DAX, explain errors, summarise findings. Always check output against row counts and a known total; AI invents column names and joins.

• Explain each project as: business question → data → cleaning → analysis → insight → recommendation → impact.
• Publish on GitHub (SQL, notebooks, README with screenshots) and Power BI public or Tableau Public. Link both from LinkedIn and your resume.
• Resume bullets use numbers: "Built a cohort model in SQL that found a 23% M1 churn spike tied to delivery delays."

• Rounds: SQL live coding → Excel or Power BI task → case or business round → behavioural.
• Case framework ("sales dropped 15%"): clarify the metric and period → check the data → segment (time, region, product, channel, customer) → internal vs external causes → recommend and say how you'd measure it.
• Metric design ("how would you measure feature X"): goal → primary metric → guardrail metric → segment → test.
• Behavioural: STAR stories for a messy dataset, a stakeholder disagreement, an insight that changed a decision, and a mistake you caught.
• Think aloud, state assumptions, sanity-check results ("does revenue per customer look plausible?").