Phase 01: SQL from raw tables
Free SQL Course for Data Analysts: 14 Hands-on Lessons
SQL from the first GROUP BY to query plans, built from raw rows and checked against Python.
This phase teaches the SQL an analyst writes every day, starting from one raw table and ending with the queries interviewers use to separate juniors from seniors: grouping and totals, joins that neither drop nor double count rows, window functions, and cohort retention. Each lesson has a business problem with a named stakeholder, a small synthetic dataset, and a trap that gives a plausible wrong number, shown failing on the lesson's own data. You prove every answer twice: once in SQL, and once in a plain Python loop inside the test. Each lesson follows the course format in LESSON_TEMPLATE.md.
The 14 lessons, in learning order
- 1. GROUP BY from raw rows
How much did each city bring in last quarter, and which cities are big enough to matter?
Level 1: Beginner · about 2 hours
- 2. Dates and the date spine
What were signups per day, including the days with zero?
Level 1: Beginner · about 2 hours
- 3. Joins without losing rows
Which customers have never ordered, and what is revenue by city without counting any order twice?
Level 1: Beginner · about 2 hours
- 4. Anti-joins, self-joins and UNION
Which customers ordered in March but not in April?
Level 1: Beginner · about 2 hours
- 5. Subqueries and CTEs
Which customers spent more than their city's average?
Level 1: Beginner · about 2 hours
- 6. Duplicates and reconciliation
After the CRM import doubled some rows, how many unique customers really signed up?
Level 2: Intermediate · about 2 hours
- 7. Window functions
How is each region tracking against its fiscal year to date, who are the top reps, and did revenue go up or down from last month?
Level 2: Intermediate · about 2 hours
- 8. Rolling averages and percent of total
What is the 7-day moving average of orders, and each region's share of revenue?
Level 2: Intermediate · about 2 hours
- 9. Sessionization from raw events
How many sessions does each user have with a 30-minute timeout, and how long is a session?
Level 2: Intermediate · about 2 hours
- 10. Gaps and islands: streaks
Which users logged a workout three days in a row, and what is the longest streak?
Level 2: Intermediate · about 2 hours
- 11. Cohort retention
Of the users who signed up in each month, what share were still active one, two and three months later?
Level 2: Intermediate · about 2 hours
- 12. Review a query someone else wrote
A teammate, or an assistant, wrote this query. Is the number on the slide right?
Level 2: Intermediate · about 2 hours
- 13. Query plans and indexes
Why does the dashboard query take 40 seconds, and what does the query plan say?
Level 3: Advanced · about 2 hours
- 14. SQL dialects for work and interviews
How do the same five queries look in SQLite, Postgres, BigQuery and Snowflake?
Level 3: Advanced · about 2 hours
What you will be able to do
By the end of the phase you will have built and tested each of these yourself, one per lesson:
- GROUP BY from raw rows: A count of orders per status, so you know what you are filtering out.
- Dates and the date spine: The IST day of each signup from a UTC timestamp, with
date(..., '+330 minutes'). - Joins without losing rows: The row-count check: count each table, then count the join, and explain the difference.
- Anti-joins, self-joins and UNION: One list of orders from two tables with UNION ALL, and a row-count check that proves nothing merged.
- Subqueries and CTEs: Customer totals: one row per customer who ordered, which add up to the order total.
- Duplicates and reconciliation: A
customer_keyfor every CRM row: trimmed, lower case, +tag removed, and a separate key for each contact with no email. - Window functions: A fiscal-year-to-date running total with
SUM(...) OVER (PARTITION BY ... ORDER BY ...). - Rolling averages and percent of total: Orders and revenue for every day of the period, zero days included, from a recursive date spine.
- Sessionization from raw events: The gap, in whole seconds, between each event and the same user's previous event.
- Gaps and islands: streaks: One row per user and workout day, from logs that can have several rows a day or no time at all.
- Cohort retention: Monthly signup cohorts with
strftime('%Y-%m', ...). - Review a query someone else wrote: A review of
slide_query.sql: one cheap check per suspicion, each run on the lesson's data. - Query plans and indexes:
plan(): the plan of any query, as a list of lines. - SQL dialects for work and interviews: Five portable queries in SQLite: monthly revenue, weekly orders, average order value with a promo share, each customer's latest order, and days to the second order.
How to start
Clone the course and run the first lesson's test. It fails until your exercise is right. You need only Python 3.9 or later.
git clone https://github.com/Ani19arya/analytics-from-scratch
cd analytics-from-scratch/phases/01-sql/01-group-by-from-raw-rows
python3 test_lesson.pyJobs that ask for these skills
Open roles on Real Analyst Jobs where this phase's skills come up, read daily from company career pages:
