RoleGPSThe free map to your AI job
All capabilities · Data engineering & analytics

Query and model data with SQL

Joins, aggregations, window functions, CTEs; answer business questions from relational data.

~25 focused hoursbeginner
Explore 4 tools for this project
Market relevance

Which roles ask for this — and how often

Share of job postings in India, per role, that name this capability.

AI-enabled Data Analyst83%AI Technical Trainer / Instructor43%AI Support Engineer34%AI Product Manager10%
What employers mean

You should be able to…

  1. Write multi-table joins to answer a business question from raw relational data
  2. Use window functions (RANK, LAG/LEAD, running totals) for cohort and trend analysis
  3. Write CTEs to break a gnarly query into readable steps instead of nested subqueries
  4. Aggregate and group data correctly (avoid double-counting from fan-out joins)
  5. Optimize a slow query using EXPLAIN and appropriate indexes
  6. Validate query output against a known ground truth before shipping a number
  7. Translate a vague stakeholder ask ('why did signups drop') into a precise SQL question
Learn — free, link-checked

The few resources that matter

Read · beginner · 20 min · postgresql.org

The SQL Language — Joins Between Tables

Canonical explanation of inner/outer/cross joins from the database you'll actually be tested on in interviews. — PostgreSQL Global Development Group
Course · beginner · 150 min · mode.com

SQL Tutorial

Interactive, browser-based SQL lessons that go from SELECT basics to window functions using real business questions — no local setup. — Mode Analytics
Watch · beginner · 260 min · youtube.com

SQL Tutorial - Full Database Course for Beginners

One sitting from installing a database to joins, subqueries, and schema design — the fastest way to get query-writing reps in. — freeCodeCamp.org
Read · intermediate · 30 min · postgresql.org

The SQL Language — Window Functions

Official reference for RANK, LAG, and running totals — the SQL feature most Indian AI-analyst interviews probe for. — PostgreSQL Global Development Group
Tools for practice

Choose a tool for the job

Start with one tool for each part of your project. You don’t need to learn them all.

Go to the practice brief

4 tools to explore

PostgreSQL

Data · Build

Practise SQL joins and aggregations, or store application records in a relational database.

Visit toolGetting started

Practices & references

  • Window functions
  • Common table expressions

Selected for practice by RoleGPS. No paid placements or affiliate links.
Access can change; check the details before starting.

Think your tool is a good fit? Let us know

Practice

Multi-table SQL analysis of a 100k-order e-commerce dataset

Load the Olist e-commerce dataset (nine related CSVs: orders, order_items, payments, reviews, customers, sellers, products) into Postgres and answer 10 business questions purely in SQL — daily order trend, top delivery-delay causes, repeat customers, and a window-function query flagging customers who place 5+ orders inside a 10-minute window. The payments and order_items tables both fan out from orders, so at least one question is a trap you have to spot and fix rather than double-count revenue. Keep every query in one reproducible .sql file with comments explaining the logic and the intended grain.

Start from

Brazilian E-Commerce Public Dataset by Olist — ~100k orders across 9 related CSVs (orders, items, payments, reviews, customers, sellers, products)

Milestones
  1. Script the schema, load all 9 CSVs into Postgres, verify row counts · ~3.5h
  2. Answer the 5 straightforward join/aggregate questions and check one for fan-out double-counting · ~5h
  3. Write the window-function and CTE queries (running totals, ranking within group, velocity flag) · ~5h
  4. EXPLAIN the slowest query, add an index, record the before/after and write the README · ~4.5h
Done when
  • Schema and seed data are scripted (not manually inserted) so the project is reproducible from scratch
  • At least 3 queries use window functions and at least 2 use CTEs
  • One query correctly flags a velocity-based anomaly (multiple transactions in a short window per payer)
  • README documents each query's business question and includes the query plan (EXPLAIN output) for the slowest one
Prove it

Evidence a recruiter can check

  • A single commented `analysis.sql` where each query is headed by the business question it answers and the grain it returns
  • The double-counting worked example: the naive revenue query, the number it produces, the corrected query, and the gap between them
  • EXPLAIN output before and after adding an index, with the runtime change stated in the README
  • A seed script that rebuilds the whole database from the raw CSVs in one command
Signal it

Answered 10 business questions on a 100k-order relational dataset in pure SQL — including a fan-out join that was inflating revenue, and an index that cut the slowest query's runtime.

Interview

Questions you'll get asked

  1. Write a query to find the second-highest salary per department using window functions.
  2. Given orders and customers tables, find customers with no orders in the last 90 days.
  3. What's the difference between RANK, DENSE_RANK, and ROW_NUMBER? When would each cause a bug?
  4. How would you find and de-duplicate rows that are logically the same but differ in one column?
  5. Explain the difference between WHERE and HAVING with an example.
  6. A join is returning more rows than expected — how do you debug it?
  7. Write a query for month-over-month retention using a self-join or window function.
  8. How would you find the top 3 products by revenue within each category (not overall)?
See where you stand for AI-enabled Data Analyst