InterviewSkill
Query concepts for analytics, backend, data engineering, and ML roles.
WHERE filters rows before grouping; HAVING filters groups after aggregation.
WHERE cannot filter aggregate results because it runs before GROUP BY. HAVING can filter values like COUNT, SUM, and AVG.
INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN are common join types.
Inner joins return matches, outer joins preserve unmatched rows, and cross joins return every row combination.
It calculates across related rows without collapsing them.
Window functions use OVER with optional PARTITION BY and ORDER BY for rankings, running totals, moving averages, and row comparisons.
A common table expression is a named temporary result inside a query.
CTEs make complex SQL easier to read by breaking logic into named steps. Some databases may materialize them, while others optimize them inline.
DELETE removes rows, TRUNCATE clears all rows, and DROP removes the table.
DELETE can filter rows and often logs row changes. TRUNCATE is faster for clearing a table. DROP removes the schema object and data.
Use a ranking window function or a subquery that finds the maximum below the overall maximum.
DENSE_RANK is often safest when duplicates matter: rank salaries descending, then filter rank = 2. Clarify whether ties should count as one rank.
Group by the duplicate-defining columns and filter groups with COUNT(*) greater than one.
The key is defining what duplicate means. For full-row duplicates, group by all relevant columns. For business duplicates, group by natural keys like email or order id.
NULL represents unknown, so comparisons like = NULL do not evaluate to true.
Use IS NULL or IS NOT NULL. NULL also affects joins, filters, aggregates, and three-valued logic, so explicit handling prevents subtle bugs.