InterviewDB Experience · USA

Employee Address Lookup - Multi-table SQL Join and Data Cleaning

phone
Sign in to bookmark

Interview Experience

Problem

You have three tables:

sql
employees(id INT, name VARCHAR, department_id INT)
addresses(employee_id INT, street VARCHAR, city VARCHAR, country VARCHAR, is_primary BOOLEAN)
departments(id INT, name VARCHAR, location VARCHAR)

Write SQL queries for:

Q1:

Return each employee's name, their primary address city, and their department name. Include employees with no address on file (show NULL for city).

Q2: Find all employees whose primary address country differs from their department's location country.

Q3: Some employees have multiple rows marked is_primary = TRUE (data error).

Return a list of those employee IDs and the count of duplicate primary addresses.

Example output for Q3:

employee_id | duplicate_count
------------+----------------
       1042 |              2
       2871 |              3

Follow-ups

  1. How would you fix the duplicate primary addresses in a single UPDATE/DELETE statement?
  2. What index would you add to make Q2 performant on a 5M-row addresses table?
  3. Rewrite Q1 using a CTE for readability.
  4. How would you handle employees with addresses in multiple countries?

Full Details

Problem

You have three tables:

sql
employees(id INT, name VARCHAR, department_id INT)
addresses(employee_id INT, street VARCHAR, city VARCHAR, country VARCHAR, is_primary BOOLEAN)
departments(id INT, name VARCHAR, location VARCHAR)

Write SQL queries for:

Q1:

Return each employee's name, their primary address city, and their department name. Include employees with no address on file (show NULL for city).

Q2: Find all employees whose primary address country differs from their department's location country.

Q3: Some employees have multiple rows marked is_primary = TRUE (data error).

Return a list of those employee IDs and the count of duplicate primary addresses.

Example output for Q3:

employee_id | duplicate_count
------------+----------------
       1042 |              2
       2871 |              3

Follow-ups

  1. How would you fix the duplicate primary addresses in a single UPDATE/DELETE statement?
  2. What index would you add to make Q2 performant on a 5M-row addresses table?
  3. Rewrite Q1 using a CTE for readability.
  4. How would you handle employees with addresses in multiple countries?
Free preview — 6 questions shown. Unlock all Gusto questions →

About This Question

This is a candidate experience report from a gusto interview during the phone round.

It covers the following topics: Coding, Sql, Phone, Onsite .

Topics

Coding Sql Phone Onsite

More Gusto Interview Questions

LeetCode #273 Integer to English Words InterviewDB Gusto SWE Phone - Bold Tags InterviewDB JSON Difference - Deep Compare Two JSON Objects and Return a Diff LeetCode #616 Add Bold Tag in String LeetCode #2591 Distribute Money to Maximum Children
← All Gusto questions Gusto Software Engineer Questions → All Strings Questions →

About Gusto Interview Reports

This question was reported by a candidate who interviewed at Gusto. LeakCode aggregates interview reports from 10+ sources, including 1Point3Acres, Glassdoor, LeetCode Discuss, Blind, Reddit, Indeed, and Nowcoder. Each report is translated where necessary, deduplicated against existing entries, and tagged by company, role, round type, and reporting date.

Use this question as one calibration data point, not a memorization target. Companies typically rotate their question pools every 2-4 months; the exact wording of a 2024 question may differ from what you encounter today. The underlying pattern, difficulty level, and follow-up depth at Gusto are the higher-signal extractions to take from this report.

For broader preparation context, the Gusto interview process typically includes a recruiter screen, one or two technical phone screens, and a 4-5 round on-site loop covering coding, system design (at L4+ levels), and behavioral. Reports tagged on LeakCode show the round-by-round distribution and typical difficulty calibration. To browse questions filtered by round type and seniority, use the company hub linked above.

How To Practice This Type of Question

Solve similar problems on LeetCode under timed conditions (25-35 minutes per medium difficulty). The goal is pattern recognition: recognize the underlying technique (sliding window, two-pointer, BFS, memoized recursion, etc.) within 60-90 seconds of reading. Strong candidates verbalize their hypothesis out loud before coding, then iterate based on feedback. Weak candidates dive into implementation immediately, lose time on the wrong approach, and run out of time for follow-ups.

Companies update their question pools every 2-4 months. The exact wording of any given question may have been retired by the time you interview. Focus your prep on the pattern, not the specific problem. The patterns that appear in Gusto reports consistently are the ones worth investing in; one-off niche problems are not.

During Your Gusto Round

Apply the standard interview round template: clarify requirements (2-3 minutes), state your approach out loud and confirm direction with the interviewer (3-5 minutes), code with narration (15-25 minutes), test with concrete examples including edge cases (5 minutes), discuss optimization or trade-offs if time permits (5 minutes). This template is universally accepted across FAANG and adjacent companies; deviating from it produces weaker interviewer feedback signal.

The single most predictive failure mode in Gusto reports tagged "no hire": not asking clarifying questions. Interviewers are explicitly trained to weight this. Strong candidates ask 3-5 clarifying questions even on problems that look obvious; weak candidates dive into code immediately. The clarifying-question check is often the first signal recorded in the interviewer's written notes.