{"course":{"slug":"ai-careers","title":"Working in AI","url":"https://addaly.com/learn/ai-careers","level":"beginner","track":"work"},"lesson":{"slug":"sql-is-the-gate","title":"SQL, and the six query shapes that cover the job","url":"https://addaly.com/learn/ai-careers/sql-is-the-gate","position":22,"of":85,"minutes":9,"answer":"SQL is tested in almost every data interview because it is fast to test and impossible to fake, and six query shapes cover nearly all of the work.","keyIdea":"SQL is tested in almost every data interview because it is fast to test and impossible to fake, and six query shapes cover nearly all of the work."},"body":{"format":"markdown","text":"## Why this one skill decides so many applications\n\nSQL is the shared language of analytics, data engineering and a large part of AI application work. It is also the easiest thing in the field to test: an interviewer can put a schema on a screen and know within fifteen minutes whether you have used it. That combination (universally needed, cheaply verified) is why it appears in almost every process and why it is the highest-return eight weeks a beginner can spend.\n\n## The six shapes\n\nNearly every query you will write at work is one of these, or two of them stacked.\n\n**1. Filter and aggregate.**\n\n```sql\nselect date_trunc('month', created_at) as month,\n       count(*)                        as orders,\n       count(distinct customer_id)     as customers,\n       sum(total_amount)               as revenue\nfrom orders\nwhere status <> 'cancelled'\ngroup by 1\norder by 1;\n```\n\n**2. Join two tables and keep the rows that do not match.** `left join` exists so that a customer with no orders still appears with a zero.\n\n**3. Group, then filter the groups.** `having count(*) > 3`: filtering after aggregation, which is a different operation from `where`.\n\n**4. Bucket by time.** Month, week, day, hour. Half of all business questions are a time bucket in disguise.\n\n**5. A window function.** Running totals, rank within a group, comparing a row to the previous one.\n\n```sql\nselect customer_id, order_date, total_amount,\n       sum(total_amount) over (partition by customer_id\n                              order by order_date) as running_total\nfrom orders;\n```\n\n**6. Second-highest, latest-per-group, first-touch.** The classic interview family, solved with a window function or a correlated subquery.\n\nIf you can write all six against a schema you have not seen before, you can pass most SQL interviews.\n\n## The two bugs that cost real money\n\n**Join fan-out.** You join `orders` to `order_items` to filter by product, then sum `orders.total_amount`. An order with three items now appears three times, and revenue is inflated by however many items the average order has. The query runs, the number looks plausible, and the dashboard is wrong for eight months.\n\nThe mechanism: a join multiplies rows whenever the right side has more than one match. Aggregating a left-table column after such a join counts it once per match.\n\n> Figure: Join fan-out: one order counted once per item. Bars (revenue in lakh rupees, summed from orders.total_amount): True revenue, from orders alone 4.8; Same column after joining order_items 12.5. A thousand orders averaging 2.6 items each. Join orders to order_items to filter by product, sum orders.total_amount, and every order is counted once per matching item. The query runs, the number looks plausible, and the dashboard is wrong for eight months.\n\nThe habit that prevents it: check row counts before and after every join. If the count changed and you did not expect it to, stop.\n\n**NULL is not a value.** `where status <> 'cancelled'` silently drops every row where `status` is NULL, because comparing anything to NULL yields *unknown*, and `where` keeps only *true*. This is not a quirk to memorise; it is three-valued logic, and it will remove customers, orders and patients from your results without any error.\n\nWrite `where status is distinct from 'cancelled'` or `where (status <> 'cancelled' or status is null)` when NULL should be kept, and always know which you meant.\n\n## Practising without a cloud account\n\nJob adverts name Snowflake, BigQuery and Databricks. You do not need any of them, and their free tiers all end. The SQL is the same.\n\nThe free path:\n\n- **DuckDB**: one binary, no server, and it queries CSV and Parquet files directly. `duckdb -c \"select count(*) from 'sales.csv'\"` works on a file you downloaded five seconds ago.\n- **SQLite**: already on your machine, and enough for everything in this lesson except window functions on very old versions.\n- **PostgreSQL**: one install, and the dialect closest to what most warehouses use.\n\nDialects differ mainly in date functions and string handling. Learn one properly; the translation is an afternoon.\n\n## The practice method that works\n\nDo not grind two hundred puzzle problems. Puzzle sites over-represent exotic window functions and under-represent the actual job, which is joining four tables that disagree with each other.\n\nInstead: take one real dataset with several tables. Write twenty questions a person would ask of it: *which day of the week has the most cancellations, which customers stopped buying after March, is the average order value going up or down for repeat customers*. Answer each one. Then check every answer a second way: a different query, a spot-check of five rows, or a total that must match something you already know.\n\nThe second check is the part that turns SQL knowledge into employability, because it is the habit an employer is actually buying.\n\n## Where SQL stops\n\nIt is bad at some things, and knowing where it stops is part of knowing it. Anything requiring row-by-row procedural logic, complex string parsing, or calling an external service belongs in Python. Very deep nested subqueries become unreadable long before they become slow. And a query that is correct can still be unusable if it scans a hundred million rows every time someone opens a dashboard, at which point the answer is a materialised table, not a cleverer query."},"check":{"question":"An analyst joins `orders` to `order_items` so she can filter to a product category, then reports `sum(orders.total_amount)` by month. Revenue looks about 2.4 times higher than finance's figure. What has happened?","options":[{"text":"The `order_items` table contains cancelled items that should have been filtered out first.","correct":false,"tempting":"A plausible data-quality guess that would shift the number slightly, not multiply it by roughly the average items per order."},{"text":"Finance is using a different currency conversion date, so the two figures are measuring different things.","correct":false,"tempting":"Reaches for a reconciliation difference between teams, which is common in general but does not explain a clean multiplicative gap."},{"text":"The join produced one row per order item, so each order's total is counted once for every item it contains, and summing a left-table column after the join multiplies revenue by the average number of items per order.","correct":true},{"text":"Window functions are needed here, and the `group by` is aggregating at the wrong level.","correct":false,"tempting":"Names a more advanced tool as the fix, when the problem is the row count entering the aggregate, not the aggregate itself."}]},"prev":{"slug":"stuck-after-the-courses","title":"When the courses end and you still freeze","url":"https://addaly.com/learn/ai-careers/stuck-after-the-courses"},"next":{"slug":"python-that-earns","title":"The employable half of Python","url":"https://addaly.com/learn/ai-careers/python-that-earns"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'SQL, and the six query shapes that cover the job', in Working in AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/ai-careers/sql-is-the-gate (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"SQL, and the six query shapes that cover the job","courseTitle":"Working in AI","module":{"index":3,"slug":"the-evidence","title":"The evidence: what gets tested, and how to prove you have it"},"answer":null,"keyIdea":"SQL is tested in almost every data interview because it is fast to test and impossible to fake, and six query shapes cover nearly all of the work.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"bars","unit":"revenue in lakh rupees, summed from orders.total_amount","title":"Join fan-out: one order counted once per item","series":[{"label":"True revenue, from orders alone","value":4.8},{"label":"Same column after joining order_items","value":12.5,"highlight":true}],"caption":"A thousand orders averaging 2.6 items each. Join orders to order_items to filter by product, sum orders.total_amount, and every order is counted once per matching item. The query runs, the number looks plausible, and the dashboard is wrong for eight months."},"alt":"Figure: Join fan-out: one order counted once per item. Bars (revenue in lakh rupees, summed from orders.total_amount): True revenue, from orders alone 4.8; Same column after joining order_items 12.5. A thousand orders averaging 2.6 items each. Join orders to order_items to filter by product, sum orders.total_amount, and every order is counted once per matching item. The query runs, the number looks plausible, and the dashboard is wrong for eight months."}},"blocks":[{"t":"h2","id":"why-this-one-skill-decides-so-many-applications","runs":[{"text":"Why this one skill decides so many applications"}]},{"t":"p","runs":[{"text":"SQL is the shared language of analytics, data engineering and a large part of AI application work. It is also the easiest thing in the field to test: an interviewer can put a schema on a screen and know within fifteen minutes whether you have used it. That combination (universally needed, cheaply verified) is why it appears in almost every process and why it is the highest-return eight weeks a beginner can spend."}]},{"t":"h2","id":"the-six-shapes","runs":[{"text":"The six shapes"}]},{"t":"p","runs":[{"text":"Nearly every query you will write at work is one of these, or two of them stacked."}]},{"t":"p","runs":[{"text":"1. Filter and aggregate.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"select date_trunc('month', created_at) as month,\n       count(*)                        as orders,\n       count(distinct customer_id)     as customers,\n       sum(total_amount)               as revenue\nfrom orders\nwhere status <> 'cancelled'\ngroup by 1\norder by 1;"},{"t":"p","runs":[{"text":"2. Join two tables and keep the rows that do not match.","b":true},{"text":" "},{"text":"left join","code":true},{"text":" exists so that a customer with no orders still appears with a zero."}]},{"t":"p","runs":[{"text":"3. Group, then filter the groups.","b":true},{"text":" "},{"text":"having count(*) > 3","code":true},{"text":": filtering after aggregation, which is a different operation from "},{"text":"where","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"4. Bucket by time.","b":true},{"text":" Month, week, day, hour. Half of all business questions are a time bucket in disguise."}]},{"t":"p","runs":[{"text":"5. A window function.","b":true},{"text":" Running totals, rank within a group, comparing a row to the previous one."}]},{"t":"code","lang":"sql","label":"SQL","text":"select customer_id, order_date, total_amount,\n       sum(total_amount) over (partition by customer_id\n                              order by order_date) as running_total\nfrom orders;"},{"t":"p","runs":[{"text":"6. Second-highest, latest-per-group, first-touch.","b":true},{"text":" The classic interview family, solved with a window function or a correlated subquery."}]},{"t":"p","runs":[{"text":"If you can write all six against a schema you have not seen before, you can pass most SQL interviews."}]},{"t":"h2","id":"the-two-bugs-that-cost-real-money","runs":[{"text":"The two bugs that cost real money"}]},{"t":"p","runs":[{"text":"Join fan-out.","b":true},{"text":" You join "},{"text":"orders","code":true},{"text":" to "},{"text":"order_items","code":true},{"text":" to filter by product, then sum "},{"text":"orders.total_amount","code":true},{"text":". An order with three items now appears three times, and revenue is inflated by however many items the average order has. The query runs, the number looks plausible, and the dashboard is wrong for eight months."}]},{"t":"p","runs":[{"text":"The mechanism: a join multiplies rows whenever the right side has more than one match. Aggregating a left-table column after such a join counts it once per match."}]},{"t":"p","runs":[{"text":"The habit that prevents it: check row counts before and after every join. If the count changed and you did not expect it to, stop."}]},{"t":"p","runs":[{"text":"NULL is not a value.","b":true},{"text":" "},{"text":"where status <> 'cancelled'","code":true},{"text":" silently drops every row where "},{"text":"status","code":true},{"text":" is NULL, because comparing anything to NULL yields "},{"text":"unknown","i":true},{"text":", and "},{"text":"where","code":true},{"text":" keeps only "},{"text":"true","i":true},{"text":". This is not a quirk to memorise; it is three-valued logic, and it will remove customers, orders and patients from your results without any error."}]},{"t":"p","runs":[{"text":"Write "},{"text":"where status is distinct from 'cancelled'","code":true},{"text":" or "},{"text":"where (status <> 'cancelled' or status is null)","code":true},{"text":" when NULL should be kept, and always know which you meant."}]},{"t":"h2","id":"practising-without-a-cloud-account","runs":[{"text":"Practising without a cloud account"}]},{"t":"p","runs":[{"text":"Job adverts name Snowflake, BigQuery and Databricks. You do not need any of them, and their free tiers all end. The SQL is the same."}]},{"t":"p","runs":[{"text":"The free path:"}]},{"t":"ul","items":[[{"text":"DuckDB","b":true},{"text":": one binary, no server, and it queries CSV and Parquet files directly. "},{"text":"duckdb -c \"select count(*) from 'sales.csv'\"","code":true},{"text":" works on a file you downloaded five seconds ago."}],[{"text":"SQLite","b":true},{"text":": already on your machine, and enough for everything in this lesson except window functions on very old versions."}],[{"text":"PostgreSQL","b":true},{"text":": one install, and the dialect closest to what most warehouses use."}]]},{"t":"p","runs":[{"text":"Dialects differ mainly in date functions and string handling. Learn one properly; the translation is an afternoon."}]},{"t":"h2","id":"the-practice-method-that-works","runs":[{"text":"The practice method that works"}]},{"t":"p","runs":[{"text":"Do not grind two hundred puzzle problems. Puzzle sites over-represent exotic window functions and under-represent the actual job, which is joining four tables that disagree with each other."}]},{"t":"p","runs":[{"text":"Instead: take one real dataset with several tables. Write twenty questions a person would ask of it: "},{"text":"which day of the week has the most cancellations, which customers stopped buying after March, is the average order value going up or down for repeat customers","i":true},{"text":". Answer each one. Then check every answer a second way: a different query, a spot-check of five rows, or a total that must match something you already know."}]},{"t":"p","runs":[{"text":"The second check is the part that turns SQL knowledge into employability, because it is the habit an employer is actually buying."}]},{"t":"h2","id":"where-sql-stops","runs":[{"text":"Where SQL stops"}]},{"t":"p","runs":[{"text":"It is bad at some things, and knowing where it stops is part of knowing it. Anything requiring row-by-row procedural logic, complex string parsing, or calling an external service belongs in Python. Very deep nested subqueries become unreadable long before they become slow. And a query that is correct can still be unusable if it scans a hundred million rows every time someone opens a dashboard, at which point the answer is a materialised table, not a cleverer query."}]}],"check":{"question":[{"text":"An analyst joins "},{"text":"orders","code":true},{"text":" to "},{"text":"order_items","code":true},{"text":" so she can filter to a product category, then reports "},{"text":"sum(orders.total_amount)","code":true},{"text":" by month. Revenue looks about 2.4 times higher than finance’s figure. What has happened?"}],"options":[{"text":[{"text":"The "},{"text":"order_items","code":true},{"text":" table contains cancelled items that should have been filtered out first."}],"correct":false,"tempting":[{"text":"A plausible data-quality guess that would shift the number slightly, not multiply it by roughly the average items per order."}]},{"text":[{"text":"Finance is using a different currency conversion date, so the two figures are measuring different things."}],"correct":false,"tempting":[{"text":"Reaches for a reconciliation difference between teams, which is common in general but does not explain a clean multiplicative gap."}]},{"text":[{"text":"The join produced one row per order item, so each order’s total is counted once for every item it contains, and summing a left-table column after the join multiplies revenue by the average number of items per order."}],"correct":true,"tempting":null},{"text":[{"text":"Window functions are needed here, and the "},{"text":"group by","code":true},{"text":" is aggregating at the wrong level."}],"correct":false,"tempting":[{"text":"Names a more advanced tool as the fix, when the problem is the row count entering the aggregate, not the aggregate itself."}]}],"order":[0,1,3,2],"why":null},"prev":{"slug":"stuck-after-the-courses","title":"When the courses end and you still freeze","minutes":7},"next":{"slug":"python-that-earns","title":"The employable half of Python","minutes":9}}}