Topics
SQL Topics
Every interview question on SQLBuddy belongs to a topic. Pick a topic to learn the concept, see the SQL pattern behind it, and practice with real questions.
Window Functions
Window functions compute a value across a set of rows related to the current row without collapsing them into a single output row. They are the workhorse of modern SQL interviews: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions used with OVER.
16 practice questions
GROUP BY
GROUP BY collapses rows that share column values and lets you run aggregate functions (COUNT, SUM, AVG, MIN, MAX) per group. Most interview questions that mention 'per X', 'each Y', or 'by Z' reduce to a GROUP BY.
10 practice questions
HAVING
HAVING filters groups after aggregation, exactly like WHERE filters rows before it. Any condition on an aggregate — a count above 5, an average above the company average — belongs in HAVING.
4 practice questions
SQL JOINs
JOINs combine rows from two tables on a relationship. INNER JOIN keeps only matches, LEFT JOIN keeps all left rows and fills unmatched with NULL, and a self join joins a table to itself. Anti-joins (NOT EXISTS / LEFT JOIN ... IS NULL) find rows with no match.
7 practice questions
Self Join
A self join joins a table to itself, using aliases to treat the same table as two roles. It is the classic tool for comparing rows within one table: employees to their managers, weather to the previous day, machines to their own process steps.
3 practice questions
Subqueries
A subquery is a SELECT nested inside another query — in FROM (a derived table), in WHERE (scalar or IN comparisons), or in SELECT (a scalar expression). Subqueries are the building block for 'second highest', 'above average', and 'customers who did X'.
6 practice questions
Common Table Expressions (CTEs)
A CTE (WITH clause) names a subquery so later parts of the query can reference it. CTEs make multi-step problems readable: dedupe first, number rows second, filter last.
3 practice questions
Gaps and Islands
The gaps-and-islands pattern finds contiguous runs ('islands') of rows — consecutive dates, consecutive IDs — by subtracting a row number from a date to bucket each run. It answers 'longest streak', 'consecutive logins', and 'missing dates'.
1 practice question
Date Functions
SQLite's date toolkit is small: strftime for formatting, julianday for day differences, date for arithmetic. Most interview date problems are really date arithmetic: 'the day after first login', 'gap in days between orders'.
8 practice questions
Aggregation
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one summary value, usually combined with GROUP BY. Mastery means knowing what each one does with NULLs and duplicates.
11 practice questions
Ranking
Ranking questions use RANK, DENSE_RANK, and ROW_NUMBER to order rows within partitions — top N per group, nth highest salary, leaderboards with ties.
Concept guide
Running Totals
A running total accumulates a value across ordered rows with SUM() OVER (... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Rolling averages are the same pattern with AVG and a bounded frame.
Concept guide
Conditional Aggregation
Conditional aggregation uses CASE WHEN inside an aggregate to compute different buckets in one pass — the standard pivot technique: SUM(CASE WHEN quarter = 1 THEN revenue END) AS q1.
1 practice question
CASE WHEN
CASE WHEN evaluates conditions in order and returns the first match — SQL's if/else. It handles conditional logic, bucketing values, swapping rows, and pivot values.
2 practice questions
Duplicate Handling
Duplicate questions test your grasp of DISTINCT, GROUP BY + HAVING COUNT(*) > 1, and ROW_NUMBER for deleting or keeping one row per group.
7 practice questions
NULL Handling
NULL is 'unknown', not a value: it never equals anything, never matches a join, and sorts first in ascending order. Interviewers test NULL awareness with COALESCE, IS NULL, NOT IN traps, and aggregate behavior.
8 practice questions
String Functions
String questions use LENGTH, SUBSTR, REPLACE, UPPER/LOWER, TRIM, and LIKE to inspect and transform text columns.
1 practice question
SELECT and WHERE
The foundation of every query: choosing columns with SELECT and filtering rows with WHERE. Easy interview questions are almost always a clean SELECT ... WHERE with a twist like NULL-safe comparison or OR logic.
5 practice questions
Set Operations
UNION, UNION ALL, INTERSECT, and EXCEPT combine results of separate queries. UNION ALL is the tool for stacking rows from two sides — like counting friend relationships in both directions.
1 practice question