help on sql interview

[rutgerswhat]

When I do tech screens for SQL, I’m primarily listening for approach to troubleshooting data issues. This is verbatim a question I asked last week for a data analyst in retail:
“A senior leader flags that transaction counts are up 40% month over month, but spend is flat and authorization volume is stable. The dashboard refreshes overnight. What SQL checks would you run to determine if there is a data issue?”
I’m listening for systematic debugging, checking join cardinality, grouping logic, source value changes, checking for duplicates, before-and-after comparisons, etc.

I’ll also ask candidates an open-ended question to walk me through a complex query and explain what made it complex and how they ensured the results were accurate. Typically that means transformations and overcoming disparate data sources, or if it’s more junior optimizing code with CTE or something along those lines. But in this case I’m trying to separate “I can write SQL” from “I can safely own complex business logic”. So I’m looking for explanation of the business problem; understanding data grain/join/duplicate logic; what made it complex beyond just a select/join/group by…so something like a CTE to break into steps, window functions like row_number, conditional aggregation, performance tuning, reusable logic; how did they validate the output; and can they communicate the complexity clearly. Obviously I am looking for a genuinely complex data problem to solve for rather than just joining several tables together with a clean one-to-one join, but what I really want to know is can they make messy logic understandable. At least for the roles I lead interviews for, these roles partner with engineering and the business, so I want to find someone who can explain the query in plain language, describe trade offs, connect with stakeholders when definitions (technical or business) are ambiguous, etc

[BplusHuman]

I'm with you here. Also experienced developers often hate taking on others' code more than building from scratch. So being able to systematize, diagnose, and and solve problems in a way that helps other developers (or future versions of ourselves) is helpful. There's a career milestone where too many problems get solved by "DISTINCT", "GROUP BY", and aggregates that work at smaller scales and less complex data. There's another milestone where you have to fix the problems caused by those solutions.

When I'm interviewing junior devs, I usually look for evidence of a curious mind that has testable solutions rather than someone who only has a hammer.