Pregunta de entrevista de autofleet
SQL Challenge (During Technical Interview): Validate Company Names Using TIN You are given two tables representing internal client records and external tax authority data. 🔹 internal_clients id | tin | company_name ---|------------|----------------------------- 1 | 123456789 | Google Limited Services 2 | 987654321 | Apple International Inc 3 | 567890123 | Amazon Logistics LLC 4 | 111222333 | Meta Platforms Ltd 🔹 external_tax_office tin | registered_name -----------|---------------------------- 123456789 | Google Services LTD 567890123 | Amazon Logistics LLC 999888777 | Netflix Corp Write a SQL query that: Compares the TINs from both tables. Returns a result showing: Which TINs exist in both tables Which TINs exist only in one table Label each row with one of the following: 'match' → TIN exists in both tables 'internal_only' → TIN exists only in internal_clients 'external_only' → TIN exists only in external_tax_office Your output should have just 2 columns: tin, source Paste your final query below.
Respuesta de la entrevista
Anónimo
SELECT COALESCE(i.tin, e.tin) AS tin, CASE WHEN i.tin IS NOT NULL AND e.tin IS NOT NULL THEN 'match' WHEN i.tin IS NOT NULL AND e.tin IS NULL THEN 'internal_only' WHEN i.tin IS NULL AND e.tin IS NOT NULL THEN 'external_only' END AS source FROM internal_clients i FULL OUTER JOIN external_tax_office e ON i.tin = e.tin; Explanation FULL OUTER JOIN – Ensures we get: TINs present in both tables. TINs present only in internal_clients. TINs present only in external_tax_office. COALESCE(i.tin, e.tin) – Combines tin from both tables into one column. CASE statement – Labels each row: 'match' if TIN is in both tables. 'internal_only' if only in internal_clients. 'external_only' if only in external_tax_office.