SQL, from your first SELECT to interview problems

SELECT    columns, aggregates
FROM      table
JOIN      other_table ON key = key
WHERE     row filter
GROUP BY  columns
HAVING    group filter
ORDER BY  columns
LIMIT     n;

1 FROM / JOIN   build the rows
2 WHERE         keep some rows
3 GROUP BY      make groups
4 HAVING        keep some groups
5 SELECT        work out the columns (and window functions)
6 DISTINCT      drop duplicate rows
7 ORDER BY      sort
8 LIMIT         cut

INNER JOIN   only rows that match on both sides
LEFT JOIN    every left row; NULLs where the right has no match
FULL JOIN    every row from both sides
CROSS JOIN   every left row paired with every right row
anti-join    LEFT JOIN … WHERE right.key IS NULL  (or NOT EXISTS)

x = NULL            never true: use x IS NULL
NULL + 1            NULL
COUNT(*)            counts rows; COUNT(col) skips NULLs
SUM / AVG / MIN / MAX   skip NULLs
COALESCE(x, 0)      first value that is not NULL
x NOT IN (…, NULL)  returns no rows: use NOT EXISTS

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY group_col ORDER BY value DESC) AS rn
  FROM t
)
SELECT * FROM ranked WHERE rn <= 3;

ROW_NUMBER() OVER (PARTITION BY g ORDER BY x)   1,2,3,4
RANK()       …                                 1,2,2,4
DENSE_RANK() …                                 1,2,2,3
LAG(x) OVER (ORDER BY d)                        previous row
SUM(x) OVER (ORDER BY d)                        running total
AVG(x) OVER (ORDER BY d ROWS 2 PRECEDING)      3-row moving average