SQL for Data Analysis with DataGrip
Answer real business questions with SQL: filter, summarise, join and rank a shop's sales data in DataGrip, then export the results to share.
Beginner · Data Science · about 10 hours · 4 modules · certificate on completion
Taught by Ngo Thi Huừngé, Valentinà Lopèz, AI Learning Companions.
About this course
Learn SQL the way data analysts use it: to answer questions. You'll work with the sales data of a small online shop, with customers, products, orders and order items, which you build yourself in DataGrip from a starter script. Each lesson teaches a piece of SQL and then shows it in DataGrip step by step, with the keyboard shortcuts for macOS, Windows and Linux: writing queries with completion, running them, and sorting, filtering and exporting the results. You'll filter and sort rows, summarise them with aggregates and GROUP BY, combine tables with joins without double counting, break hard questions into steps with subqueries and common table expressions, and rank and compare rows with window functions. Every module ends with a lab whose exact results you can check your answers against, and the course ends with a report for the shop's owner, written in SQL and explained in plain language.
What you'll learn
- Connect DataGrip to a SQLite database and build it from a SQL script
- Filter and sort rows with SELECT, WHERE and ORDER BY, and handle missing values correctly
- Summarise data with aggregate functions, GROUP BY and HAVING, and export the results
- Combine tables with inner and outer joins without double counting
- Break hard questions into steps with subqueries and common table expressions
- Rank rows and work out running totals and monthly changes with window functions
Skills you'll gain
SQL, Data analysis, Aggregation and GROUP BY, Joins, Subqueries and CTEs, Window functions, DataGrip, SQLite
Tools
DataGrip, SQLite
Before you start
No SQL experience needed: if you've used a spreadsheet, you're ready. You need a Windows, macOS or Linux computer with DataGrip installed (a free trial is enough, and JetBrains offers free licences in some cases, such as for students). DataGrip downloads the SQLite driver for you.
Modules
Connect DataGrip and ask your first questions
Learn how a relational database organises data, build the shop database in DataGrip from a script, and write your first queries with SELECT, WHERE and ORDER BY.
- Tables, keys and SQL (10 min)
- Build the shop database from a script (12 min)
- SELECT, WHERE and ORDER BY (13 min)
- Hands-on lab: Build the shop database and ask your first questions
Summarise data with aggregates and GROUP BY
Turn rows into answers: count, total and average with aggregate functions, group results by category, month or status, filter the groups with HAVING, and export a summary to share.
- Aggregate functions: COUNT, SUM, AVG, MIN and MAX (11 min)
- GROUP BY and HAVING (13 min)
- Export and share results (10 min)
- Hands-on lab: Summarise the shop's customers, products and orders
Combine tables with joins
Answer questions that span tables: join orders to customers and products, find what's missing with outer joins, and avoid the double counting that joins can cause.
- Inner joins (12 min)
- Outer joins and missing rows (12 min)
- Join pitfalls: double counting and checking results (11 min)
- Hands-on lab: Join the shop's tables
Subqueries, CTEs and window functions
Break hard questions into steps with subqueries and common table expressions, then rank rows and compute running totals and month-on-month changes with window functions.
- Subqueries (12 min)
- Common table expressions (12 min)
- Window functions: ranks, running totals and changes (14 min)
- Hands-on lab: Write the queries behind a monthly sales report
Project: Write a sales report for the shop's owner
The shop's owner wants to understand the first half of 2025. Using your shop data source in DataGrip, answer the eight questions in the starter file report.sql, writing one query under each question and keeping the question as its comment. Leave cancelled orders out of every revenue figure. Across the file, use at least one inner join, one outer join, one GROUP BY with HAVING, one CTE and one window function, wherever each fits best. Export the results of questions 3 and 7 as CSV files. Then write a summary for the owner, who doesn't know SQL: what sold best, how sales changed from month to month, which customers matter most, and anything that needs attention, such as cancellations, with at least one recommendation the data supports. To check your work: Games brings in 31.8% of revenue, and the cancelled orders were worth 126.0 between them.
Part of
DataGrip is a trademark of JetBrains s.r.o. This course is independent: JetBrains hasn't made or endorsed it.