SQL Red Flags and Common Traps to Avoid

Star vs Snowflake Schema: Database Design for Analytics explains how analytical data is organised; SQL (Structured Query Language) red flags explain what can still go wrong when querying that data. In interviews, these traps cost rounds because they silently create wrong counts, bad aggregations, slow queries, or misleading results. This checklist focuses on the mistakes to avoid and the correct habits to replace them.

Big Picture - SQL Red Flags Interviewers Look For

The checklist starts with the red flags that cost interview rounds, then maps each SQL trap to what goes wrong and the correct approach. The core interview habit is to confirm the JOIN, the granularity, the filter stage, NULL handling, window frame behaviour, and data types before trusting the result.

SQL Trap - What Goes Wrong and Correct Approach

SQL Red Flags - These Cost You Interview Rounds

These are the traps that signal weak SQL reasoning even when the query looks syntactically valid. Use them as a pre-submission checklist in analytics interviews.

1. Using DISTINCT to 'fix' duplicate rows instead of fixing the underlying JOIN, often a many-to-many join issue.
2. Not understanding granularity before GROUP BY - always ask: 'One row represents one ___?'
3. Filtering in WHERE when HAVING is needed, or vice versa - WHERE filters rows, HAVING filters groups.
4. COUNT(*) vs COUNT(column) - COUNT(*) counts all rows including NULLs; COUNT(column) skips NULLs.
5. Window function ROWS vs RANGE - ROWS counts physical rows; RANGE groups ties and can cause unexpected results.

JOIN Duplicates and DISTINCT

Using DISTINCT to 'fix' duplicate rows instead of fixing the underlying JOIN is a red flag. It masks JOIN issue, hides data quality problems, wrong counts.

The correct approach is to diagnose the JOIN - find why duplicates exist, usually a one-to-many relationship. In interviews, this shows that you are checking the relationship between tables instead of only hiding the symptom in the final SELECT.

GROUP BY and Granularity

Not understanding granularity before GROUP BY creates aggregation at wrong level - mixing fact and dimension data. The question to ask before writing GROUP BY is: 'One row represents one ___?'

Granularity is the level at which each row exists in the working dataset. If that level is unclear, the aggregation can produce a result that looks clean but is calculated at the wrong level.

WHERE vs HAVING

Filtering in WHERE when HAVING is needed, or vice versa, is a common SQL trap. WHERE filters rows, HAVING filters groups.

This distinction matters because HAVING is used after aggregation, while WHERE is used before aggregation. Using the wrong one can filter the wrong thing even when the query runs.

COUNT(*) vs COUNT(column)

COUNT(*) vs COUNT(column) is a red flag because COUNT(*) counts all rows including NULLs; COUNT(column) skips NULLs. This difference can create inconsistent results when NULLs are present.

NULL in aggregations also matters because AVG() ignores NULLs, COUNT(*) includes them - inconsistent results. The correct approach is to use COALESCE before aggregating and explicitly handle NULLs.

ROWS vs RANGE in Window Functions

Window function ROWS vs RANGE is another trap: ROWS counts physical rows; RANGE groups ties and can cause unexpected results. RANGE can include unexpected duplicate rows with same value.

The correct approach is to be explicit: use ROWS BETWEEN N PRECEDING AND CURRENT ROW. This prevents ties from changing the window result unexpectedly.

Subquery in WHERE vs JOIN

A subquery in WHERE can become a performance trap when it is correlated. Correlated subquery in WHERE runs for every row - very slow.

The correct approach is to rewrite as JOIN or use CTE for performance. This is especially important in interviews where performance awareness is evaluated along with correctness.

String Comparison with Numbers

String comparison with numbers is a trap because implicit type casting causes wrong sort order: '10' < '9' alphabetically. The correct approach is to cast explicitly: CAST(column AS INTEGER) before comparison.

Conclusion

SQL red flags matter because a query can run but still produce duplicate rows, wrong counts, inconsistent results, unexpected window outputs, or slow performance. The safest habit is to diagnose JOINs, confirm granularity, handle NULLs explicitly, choose WHERE or HAVING correctly, and cast or rewrite queries when needed.