Model your product’s data and query it with SQL
Read and design a feature’s data model, then answer your own product questions in SQL, read-only, with AI-drafted SQL you know how to check.
Read and design the data model of a feature, then answer your own product questions in SQL without waiting for the data team. You learn to read a schema (tables, keys, cardinalities), model a feature with the team, choose between a relational and a document database, and write queries that filter, aggregate and combine tables without falling into NULL or join traps. Then you use window functions for ranking and trend questions. You work on a provided dataset, in your browser, read-only and without exposing personal data, with an AI that drafts SQL you know how to check. You finish with the data model of your feature and ten queries that answer your questions.
What you will be able to do
- Read a data model (tables, primary and foreign keys, cardinalities, entity-relationship diagram) and connect it to the product's screens and rules.
- Model the data of a feature (entities, attributes, relationships, keys, cardinalities) and get the model validated by the engineering team.
- Compare a relational database and a document (NoSQL) database for a product need and justify the choice based on expected queries, consistency and change.
- Write queries that filter, sort and aggregate (SELECT, WHERE, ORDER BY, GROUP BY, HAVING) while handling NULL values and dates correctly.
- Combine several tables with INNER and LEFT joins, and check that the result neither loses nor duplicates any row.
- Turn a product question into a verifiable query, using CTEs and simple window functions (ROW_NUMBER, LAG, SUM OVER).
- Query data read-only while protecting personal data, and have an AI draft SQL while checking every query before using its result.
Prerequisites
- Be comfortable with a spreadsheet (filters, pivot tables); no database or programming knowledge is required
- Tools: a recent browser is enough (SQLite in the browser); the sqlite3 command-line shell is an option if you prefer to work locally
- Recommended course before this one: Explain how a web product works, from browser to server, to place the database within the architecture.
- This course does not make you a database administrator: it covers reading a model, designing one with the team and running read queries on data you have been given access to.
Syllabus
What will I be able to do by the end of this course, and in what order?
- Course overview3 steps
Objective · By the end of this overview, you will know what you are going to produce (the data model of a feature and ten SQL queries on your product), which dataset you will practise on and which five modules take you there.
How do my product's screens translate into tables, and how do I design the tables for my next feature?
Objective · By the end of this lesson, you will be able to read a product's data model (tables, columns, types, primary and foreign keys, cardinalities) on an entity-relationship diagram, and connect each table and each link to a screen or a product rule.
- Model the data of a feature5 steps
Objective · By the end of this lesson, you will be able to go from a feature's user stories to a data model (entities, typed attributes, relationships, cardinalities, keys, lifecycle) in six steps, and present it to the engineering team with the list of questions it raises.
Objective · By the end of this lesson, you will be able to compare a relational database and a document (NoSQL) database on four criteria (shape of the data, expected queries, consistency, schema change), justify a choice for a product need and say where analysis will happen in each case.
How do I get a reliable number from a table myself, without being caught out by missing values and dates?
Objective · By the end of this lesson, you will be able to load the Atelio dataset into SQLite (in the browser or locally), explore the structure of the tables and write SELECT queries that pick columns, filter with WHERE, sort with ORDER BY and limit with LIMIT.
- Count, sum, group4 steps
Objective · By the end of this lesson, you will be able to summarise a table with aggregate functions (COUNT, SUM, AVG, MIN, MAX), compute these summaries per group with GROUP BY, filter groups with HAVING and count a subset of rows with CASE WHEN or COUNT(DISTINCT).
Objective · By the end of this lesson, you will be able to handle NULL values correctly (IS NULL, COUNT of a column, COALESCE, average over known values), group and filter by period with date functions, and check the period covered by a table before computing an indicator.
How do I combine several tables without losing rows or inflating my numbers?
Objective · By the end of this lesson, you will be able to combine two or more tables with INNER JOIN and LEFT JOIN on the right key, use table aliases, and find rows without a match (anti-join) to answer questions such as "which accounts have never scheduled an intervention?".
Objective · By the end of this lesson, you will be able to recognise and fix the two most costly join errors (row fan-out that inflates sums, and a filter placed in WHERE that cancels a LEFT JOIN), by reasoning about the grain of each table and checking the row count.
How do I go from a vague question ("are our customers active?") to a query whose result I can defend?
Objective · By the end of this lesson, you will be able to break a query into named steps with WITH (CTEs) and use three window functions (ROW_NUMBER to keep the latest row of each group, LAG to compute a change, SUM OVER for a running total or a share of the total).
Objective · By the end of this lesson, you will be able to turn a vague product question into a precise metric definition (population, event, period, exclusions, NULL handling), derive the tables and the grain, write the query step by step and sanity-check it before sharing the result.
How do I access real data without risk to production or to privacy, and how do I have an AI write SQL without getting it wrong?
Objective · By the end of this lesson, you will be able to request data access that follows least privilege (analytics database, read-only, scope, excluded columns, duration), write queries that expose only the personal data needed and know what to do with a result that contains some.
Objective · By the end of this lesson, you will be able to give an AI the schema context it needs (tables, columns, values, business rules, dialect), have it draft and explain a query, then review the query with a ten-point checklist before using its result.
Objective · By the end of this lesson, you will have produced, on your own product, the data model of a feature and ten commented SQL queries (definition, query, check), self-assessed them with a rubric, and you will leave with an exit kit and a 7-day and 30-day application plan.