Homedepot·Data Analyst·Technical Phone Screen·Intermediate

IntermediatePrefer not to say

May 2026Remote

Summary

SQL debugging round for a Data Analyst role at Home Depot. One meaty question about a broken retail analytics query, lots of edge cases baked in, and I definitely didn't catch all of them on the first pass.

Questions Asked (1)

You're given a SQL query that's supposed to return daily promo sales for mulch products during a specific promotion window, but it's returning far fewer rows than expected. Walk through why the joins are broken and rewrite it correctly, including support for both store-specific and national promotions.

Root Cause AnalysisData ModelingTechnical Trade-offs

Author's notes

This one took me a minute to untangle.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by diagnosing the row loss: check join types, join keys, and filter placement, especially for date and promo conditions. Then rewrite the query using explicit joins and a UNION or OR condition to handle both store-specific and national promotions, ensuring all filters are in the WHERE clause or ON clause appropriately.

Pro tip: Always validate row counts after each join and filter to isolate where rows are lost; use a LEFT JOIN initially to see what's missing, then switch to INNER JOIN once you confirm the logic.

1. Identify the row loss

Compare the expected row count with the actual output, and check if the loss occurs after a specific join or filter. Use SELECT COUNT(*) at each stage to pinpoint the issue.

2. Analyze join conditions and types

Examine if INNER JOINs are unintentionally excluding rows due to mismatched keys or missing data. Check if date filters are applied in the ON clause instead of WHERE, which can turn an outer join into an inner join.

3. Review promotion logic

Ensure the query accounts for both store-specific and national promotions. This may require a UNION of two subqueries or an OR condition in the join, with proper handling of NULL store IDs for national promos.

4. Rewrite the query

Use explicit JOIN syntax, place all filters in the WHERE clause (or ON for outer joins), and combine store-specific and national promotions using UNION ALL or a CASE statement. Validate with sample data.

5. Test and validate

Run the rewritten query and compare row counts and sample outputs against expected results. Check for duplicates and ensure daily granularity is preserved.

Key Points to Mention

Join types (INNER vs LEFT) and their impact on row retention
Filter placement: ON clause vs WHERE clause in outer joins
Handling NULLs in join keys for national promotions
Using UNION ALL to combine store-specific and national promotions
Date range filtering and its interaction with joins
Validating row counts at each step to isolate issues

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.