Simple SQL checklist
A social thread pinned the practical SQL essentials for business analysts — JOINs, GROUP BY, window functions like ROW_NUMBER/RANK/LAG, plus CTEs and subqueries — arguing these solve roughly 90% of common problems. (x.com).
Structured Query Language, or SQL, has a short list of commands that covers most day-to-day analysis work: combine tables, summarize rows, compare records, and break long queries into readable steps. (postgresql.org) A join is the basic move for combining data from separate tables, such as matching an orders table to a customers table on customer identification numbers. PostgreSQL’s documentation says join queries pair rows from one table with rows from another using a matching expression. (postgresql.org) `GROUP BY` is the basic move for turning many rows into totals, counts, or averages by category, such as revenue by month or orders by region. PostgreSQL describes `WHERE`, `GROUP BY`, and `HAVING` as a pipeline of transformations applied after the `FROM` clause builds the working table. (postgresql.org) Window functions solve a different problem: they calculate across related rows without collapsing the result into one row per group. PostgreSQL says window functions operate across sets of rows related to the current row, and MySQL says they return a result for each query row rather than a single grouped result. (postgresql.org) (dev.mysql.com) That is why `ROW_NUMBER`, `RANK`, and `LAG` show up so often in analyst workflows. Microsoft says `ROW_NUMBER` assigns a sequential number starting at 1 in each partition, `RANK` gives tied rows the same value, and `LAG` reads a prior row without a self-join. (learn.microsoft.com 1) (learn.microsoft.com 2) (github.com) In plain English, `ROW_NUMBER` helps with deduplication, `RANK` helps with leaderboards and tied results, and `LAG` helps with before-and-after comparisons such as month-over-month change. Those three patterns cover common business questions about “first,” “top,” and “what changed from the last row.” (learn.microsoft.com 1) (learn.microsoft.com 2) (github.com) Common table expressions, usually written with `WITH`, are less about new math than about structure. PostgreSQL says they act like temporary tables that exist for one query, and Microsoft says query results from common table expressions are not materialized by default in SQL Server. (postgresql.org) (learn.microsoft.com) Subqueries do similar work inside a larger statement, usually when one calculation needs to feed another. PostgreSQL’s table-expression documentation treats a subquery as a derived table, alongside joins and other constructs used to build the intermediate result set. (postgresql.org) The practical checklist making the rounds is not a full map of SQL, and it does not include topics like indexing, query plans, data definition language, or recursive queries. But the official documentation across PostgreSQL, MySQL, and Microsoft SQL Server supports the core idea that joins, grouping, window functions, common table expressions, and subqueries are the building blocks behind a large share of reporting and analytics queries. (postgresql.org 1) (postgresql.org 2) (postgresql.org 3)
