SQL, and the six query shapes that cover the job

Why this one skill decides so many applications

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.

The six shapes

Nearly every query you will write at work is one of these, or two of them stacked.

1. Filter and aggregate.

select date_trunc('month', created_at) as month,
       count(*)                        as orders,
       count(distinct customer_id)     as customers,
       sum(total_amount)               as revenue
from orders
where status <> 'cancelled'
group by 1
order by 1;

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.

3. Group, then filter the groups. having count(*) > 3: filtering after aggregation, which is a different operation from where.

4. Bucket by time. Month, week, day, hour. Half of all business questions are a time bucket in disguise.

5. A window function. Running totals, rank within a group, comparing a row to the previous one.

select customer_id, order_date, total_amount,
       sum(total_amount) over (partition by customer_id
                              order by order_date) as running_total
from orders;

6. Second-highest, latest-per-group, first-touch. The classic interview family, solved with a window function or a correlated subquery.

If you can write all six against a schema you have not seen before, you can pass most SQL interviews.

The two bugs that cost real money

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.

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.

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.

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.

Write 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.

Practising without a cloud account

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.

The free path:

• 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.
• SQLite: already on your machine, and enough for everything in this lesson except window functions on very old versions.
• PostgreSQL: one install, and the dialect closest to what most warehouses use.

Dialects differ mainly in date functions and string handling. Learn one properly; the translation is an afternoon.

The practice method that works

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.

Instead: 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.

The second check is the part that turns SQL knowledge into employability, because it is the habit an employer is actually buying.

Where SQL stops

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.