SQL Cheat Sheet for Interviews: Clauses, Joins, Windows, and NULL Traps

This cheat sheet is for the moment you know the business question but need the safe SQL shape. Every pattern uses the same small PostgreSQL fixture: inspect the input rows, run the query, trace which rows survive, then compare the exact output.

The examples are intentionally small enough to calculate by hand. That is the point. A query is easier to trust when you can explain every output row and then add one counterexample for NULLs, ties, time boundaries, or join fanout.

The one-screen SQL execution map

SQL is written from SELECT, but PostgreSQL reasons about the query in a logical order closer to this:

Stage	Main question
FROM and JOIN	Which source rows can meet?
WHERE	Which individual rows survive?
GROUP BY	What does one aggregate row represent?
HAVING	Which completed groups survive?
Window functions	What can be calculated across the grouped result without collapsing it again?
SELECT	Which expressions become output columns?
DISTINCT	Which complete projected rows are duplicates?
ORDER BY and LIMIT	In what deterministic order are rows returned, and where is the cutoff?

The exact optimizer plan is an implementation detail, but this logical map explains alias visibility, aggregation errors, and why HAVING is not a row filter.

Suppose the task is to return users with at least two video sessions.

Input: sessions

session_id	user_id	session_ts	content_type	duration_sec
1	1	2026-03-01 09:00	video	100
2	1	2026-03-02 09:00	article	200
3	1	2026-03-03 09:00	audio	300
4	2	2026-03-01 12:00	video	150
5	2	2026-04-01 12:00	article	250
6	3	2026-03-02 14:00	video	180
7	3	2026-03-03 14:00	video	NULL
8	3	2026-04-02 14:00	audio	225
9	5	2026-04-03 16:00	audio	200

SELECT
  user_id,
  COUNT(*) FILTER (WHERE content_type = 'video') AS video_sessions
FROM sessions
GROUP BY user_id
HAVING COUNT(*) FILTER (WHERE content_type = 'video') >= 2
ORDER BY user_id;

Output

user_id	video_sessions
3	2

For a deeper explanation of alias visibility and windows, use the SQL order of operations guide.

Filters, NULLs, and half-open date ranges

Ordinary comparison operators can return UNKNOWN when either side is NULL. In a WHERE clause, only TRUE survives. PostgreSQL's IS DISTINCT FROM gives a two-valued, NULL-aware comparison when that is the business rule.

Input: users

user_id	country
1	US
2	US
3	IN
4	IN
5	NULL

The requirement is “return users whose country is not US, including users whose country is unknown.”

SELECT user_id, country
FROM users
WHERE country IS DISTINCT FROM 'US'
ORDER BY user_id;

Output

user_id	country
3	IN
4	IN
5	NULL

country <> 'US' would omit user 5 because NULL <> 'US' is UNKNOWN. That may be correct under a different requirement. Define the population before choosing the operator. The SQL not-equal guide has a full truth-table walkthrough.

For timestamps, prefer a half-open range. The task below means every session on March 1, 2, or 3, regardless of its time of day.

Input: sessions

session_id	session_ts
1	2026-03-01 09:00
2	2026-03-02 09:00
3	2026-03-03 09:00
4	2026-03-01 12:00
5	2026-04-01 12:00
6	2026-03-02 14:00
7	2026-03-03 14:00
8	2026-04-02 14:00
9	2026-04-03 16:00

SELECT COUNT(*) AS march_1_to_3_sessions
FROM sessions
WHERE session_ts >= TIMESTAMP '2026-03-01 00:00:00'
  AND session_ts <  TIMESTAMP '2026-03-04 00:00:00';

Output

march_1_to_3_sessions
6

Date literals and timestamp types follow dialect-specific timezone rules. In production, state the business timezone and normalize boundaries before comparison.

Joins, counts, and denominators

A left join is a population tool. Put a right-side predicate in ON when the output should retain left rows that have no qualifying match.

Input: users

user_id	country
1	US
2	US
3	IN
4	IN
5	NULL

Input: relevant columns from sessions

session_id	user_id	content_type
1	1	video
2	1	article
3	1	audio
4	2	video
5	2	article
6	3	video
7	3	video
8	3	audio
9	5	audio

SELECT
  u.user_id,
  COUNT(s.session_id) AS video_sessions
FROM users AS u
LEFT JOIN sessions AS s
  ON s.user_id = u.user_id
 AND s.content_type = 'video'
GROUP BY u.user_id
ORDER BY u.user_id;

Output

user_id	video_sessions
1	1
2	1
3	2
4	0
5	0

The correct predicate placement depends on the intended population. If the task asks only for users with video sessions, an inner join or WHERE filter may be appropriate. Review SQL joins before adding DISTINCT to repair unexpected fanout.

Aggregate functions answer different denominator questions. Audit them together on the raw session table.

Input: sessions duration and user columns

session_id	user_id	duration_sec
1	1	100
2	1	200
3	1	300
4	2	150
5	2	250
6	3	180
7	3	NULL
8	3	225
9	5	200

SELECT
  COUNT(*) AS total_rows,
  COUNT(duration_sec) AS known_durations,
  COUNT(DISTINCT user_id) AS distinct_users,
  ROUND(AVG(duration_sec), 2) AS avg_known_duration
FROM sessions;

Output

total_rows	known_durations	distinct_users	avg_known_duration
9	8	4	200.63

Do not use COALESCE(duration_sec, 0) unless the product definition says an unmeasured session lasted zero seconds. The SQL COUNT guide shows how these denominators change after joins.

Window functions and tie behavior

Window functions annotate rows without collapsing the result. Make tie behavior explicit and add a deterministic output sort.

Input: user_counts

user_id	session_count
1	3
2	2
3	3
5	1

SELECT
  user_id,
  session_count,
  ROW_NUMBER() OVER (
    ORDER BY session_count DESC, user_id
  ) AS row_number,
  RANK() OVER (
    ORDER BY session_count DESC
  ) AS rank_with_gaps,
  DENSE_RANK() OVER (
    ORDER BY session_count DESC
  ) AS dense_rank
FROM user_counts
ORDER BY session_count DESC, user_id;

Output

user_id	session_count	row_number	rank_with_gaps	dense_rank
1	3	1	1	1
3	3	2	1	1
2	2	3	3	2
5	1	4	4	3

For running metrics, specify the intended frame. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW advances one physical row at a time. PostgreSQL's default RANGE behavior can include peers with the same ordering value. Add a stable secondary key when peer order matters.

FAQ

Why can a left join return more rows than the left table?

Each left row appears once for every matching right row. A one-to-many relationship therefore multiplies rows. Check the relationship before aggregating, and reduce the right side to the needed grain when multiplication is not intended.

Is NOT IN safe when the list contains NULL?

Positive IN can still find a true match, but a nonmatching value compared with a list containing NULL becomes UNKNOWN. NOT IN then removes that row in a WHERE clause. Prefer NOT EXISTS for nullable subquery keys.

Can I rely on a select alias in WHERE?

No in PostgreSQL, because WHERE is logically evaluated before SELECT. Use the original expression, a subquery, or a CTE. Some dialects allow aliases in other clauses, so verify portability instead of assuming it.

Does UNION always sort the result?

UNION removes duplicates, while UNION ALL keeps them. The engine may use a sort, hash, or another physical strategy to implement duplicate elimination. Only an outer ORDER BY guarantees output order.

What should I check before submitting a SQL answer?

State the output grain, test NULL and tie behavior, inspect one-to-many joins, use half-open timestamp boundaries, and add a deterministic sort when row order is part of the expected answer. Then compare the rendered output with the input rows rather than trusting a successful execution alone.