1. What is the difference between WHERE and HAVING clauses?
The WHERE clause is used to filter records from a result set before any groupings are made. The HAVING clause is used to filter values from a group, typically used with the GROUP BY clause. In the GhostMode ecosystem, we suggest using WHERE for row-level performance and HAVING only when aggregate filtering is essential.
2. Explain the difference between Rank() and Dense_Rank().
Both are window functions used to assign a rank. Rank() skips the next available ranking value if there's a tie (e.g., 1, 2, 2, 4), whereas Dense_Rank() does not skip (e.g., 1, 2, 2, 3). This distinction is critical in data-driven leadership roles.
3. What is a Common Table Expression (CTE)?
A CTE is a temporary result set which you can reference within another SQL statement. It makes complex queries more readable and maintainable. Pro tip: Always use CTEs for recursive queries to demonstrate senior-level technical maturity.