Data Analytics with SQL & Excel

Weeks 1–2 — the tool you will be judged on in your first job, used properly rather than by hand.

1. Structure before formulas

   One row per record, one header row, no merged cells and no blank rows. Merged cells and blank rows are the two things that break sorting, filtering and pivot tables at once.

   Example A sheet with a merged "January" banner across row 1 cannot be filtered or pivoted — the same data with a Date column works instantly.

   Practice Take any messy sheet you have and restructure it into a clean table, then sort and filter it successfully.

2. Lookup and conditionals

   VLOOKUP, INDEX-MATCH and IF are how one sheet is linked to another. XLOOKUP, where it is available, replaces VLOOKUP for the same job with fewer traps.

   Example =INDEX(price, MATCH(item, item_list, 0)) keeps working when a column is inserted to the left of the price column — a VLOOKUP in the same place returns the wrong column.

   Practice Build a sheet where entering an item name automatically pulls its price and category from a master sheet.

3. Pivot tables and charts

   A pivot table groups and summarises without formulas. It is the fastest honest answer to "how many, per month, per category".

   Example Drop Month into Rows, Category into Columns and Amount into Values and you have a monthly-by-category report in about ten seconds.

   Practice Pivot your own dataset by month and by category, and add one chart that makes the trend obvious without explanation.

Weeks 3–5 — the skill in every analytics job description, and the one that separates a candidate from the spreadsheet crowd.

1. SELECT, WHERE, ORDER BY

   The first four words of SQL. SELECT chooses columns, WHERE filters rows, ORDER BY sorts them, LIMIT caps the output.

   Example SELECT name, city FROM candidates WHERE city = 'Hyderabad' ORDER BY name LIMIT 10 — a small query that answers a real question.

   Practice Write five queries against a table of your own, each answering a question in plain English that you write down first.

2. GROUP BY and the aggregate functions

   COUNT, SUM, AVG, MIN and MAX summarise many rows into one. GROUP BY says what "per" means in your question.

   Example SELECT month, SUM(amount) FROM sales GROUP BY month ORDER BY month — and swapping SUM for COUNT answers a completely different question, which is why the plain-English version matters.

   Practice Write one query per business question: total per month, average per category, count per city, and the single worst-performing month.

3. JOIN — combining two tables

   Data lives in separate tables: sales in one, customers in another. A join brings them together on a shared key.

   Example SELECT c.name, SUM(s.amount) FROM customers c JOIN sales s ON s.customer_id = c.id GROUP BY c.name ORDER BY 2 DESC gives your top customers.

   Practice Join two of your tables, once with JOIN and once with LEFT JOIN, and explain in one line the difference in the rows that came back.

Weeks 6–8 — the part that separates an analyst from someone who can type a query. A manager asks for a number; you hand back the answer and the reason.

1. Turning a vague request into a runnable question

   "Sales are down" is not a question. Which period, compared to what, which product lines, and what counts as a sale — a good analyst writes those four decisions down before typing any SQL.

   Example "Sales are down" becomes: total amount per month for the last 12 months, split by region, excluding cancelled orders. Now a single query answers it.

   Practice Take three vague requests you have heard and write each as a precise set of columns, filters and a time window.

2. Sanity-checking your own numbers

   A wrong number presented confidently is worse than no number. Row counts, totals and a second method for the same figure are how you catch it before your manager does.

   Example If your monthly total is 10x the yearly total, the join multiplied the rows — a duplicated key in one table, and a mistake that reaches a report once a week somewhere.

   Practice For one of your queries, check the row count before and after the join and verify the total against the raw table.

3. One dashboard, thirty seconds

   A dashboard is an argument, not a data dump. If the reader needs a walkthrough, the layout is the problem.

   Example Five numbers, one trend line and one breakdown answers almost every recurring question — and every extra chart halves the chance the first one gets read.

   Practice Build a one-screen dashboard for your dataset and ask someone to tell you the story in their own words without your help.

The date handling and window functions that separate a spreadsheet export from real analysis.

1. Dates and time in SQL

   Dates are the most common filter in analytics and the one most often broken. Store them as real date columns, filter with ranges, and never compare a date to a string that only looks like one.

   Example WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' covers a whole month and still lets the database use an index on the column.

   Practice Count postings per month for the last six months, then count only the last seven days, and compare the two queries.

2. Window functions: rank without collapsing rows

   A window function computes across a set of rows while keeping every row: rank jobs inside each city, or show a row next to its group total. GROUP BY cannot do this because it collapses the rows.

   Example ROW_NUMBER() OVER (PARTITION BY city ORDER BY salary DESC) numbers the jobs within each city, so a filter of rnk = 1 gives the best-paid job per city.

   Practice List the top two roles by salary for each city, using ROW_NUMBER in a subquery and filtering on the rank.

3. Pivot a table in SQL with CASE

   Not every database has PIVOT, but CASE works everywhere: turn one column of values into several columns of counts, the same shape a spreadsheet pivot gives you.

   Example SUM(CASE WHEN type = 'remote' THEN 1 ELSE 0 END) AS remote counts the remote rows as a column, and the same trick gives onsite and hybrid in one grouped query.

   Practice Write one query showing city as rows and remote, onsite and hybrid as columns, with a count in each cell.

Read the schema, check the data, and end every analysis with a sentence a manager can repeat.

1. Reading a schema before you write a query

   Two minutes looking at the tables saves an hour of wrong results. Find the primary keys, the foreign keys that link the tables, and which columns can be NULL, before a single SELECT.

   Example SHOW CREATE TABLE jobs reveals the foreign keys, so you know jobs.company_id points at companies.id and not the other way round.

   Practice Describe three tables in a paragraph each: their keys, the columns that can be NULL, and which other tables they join to.

2. Data quality checks you run every time

   Before any number goes out, run the same four checks: duplicate keys, values outside their allowed set, dates in the future, and totals that do not match the source.

   Example SELECT email, COUNT(*) c FROM users GROUP BY email HAVING c > 1 finds duplicate accounts; a salary < 0 filter finds impossible values.

   Practice Write four check queries for one table and note how many rows each one flags.

3. Answering with a recommendation, not a chart

   A dashboard shows what happened; a recommendation says what to do. Every analysis ends with one sentence naming the action, the size of the effect, and the risk.

   Example Not 'applications fell 12%' but 'applications fell 12% after the form grew to nine fields; removing two should recover roughly 80 applications a month'.

   Practice Take one chart you have made and write the recommendation sentence that belongs under it.