Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

UNION vs UNION ALL counts

SQL data engineering interview problem. Difficulty: beginner. Pattern: Set Operations. About 8 minutes. Part of the Pro drill bank.

Combine employees.employee_id and contractors.contractor_id. Return one row with: union_count: COUNT of ids under UNION (deduplicated) union_all_count: COUNT of ids under UNION ALL (keeps overlaps) Overlapping ids in the seed are 1 and 2.

Requirements

  • Return exactly one row with two integer columns.

Examples

Input: employees employee_id | 1 | 2 | 3 | 4 | 5 | 6 | contractors contractor_id | 1 | 2 | 19 | 20 | Output: union_count | union_all_count 8 | 10 Why this passes: UNION collapses the shared 1 and 2 (6+4-2=8). UNION ALL keeps every row from both sides (10).

Topics: lakebench, sql, union, union all.

More SQL interview questions · All interview problems · Learn data engineering

beginner

UNION vs UNION ALL counts

Interview-style drill: Compare distinct UNION count vs UNION ALL count of person ids.

Combine `employees.employee_id` and `contractors.contractor_id`. Return one row with: - `union_count`: COUNT of ids under UNION (deduplicated) - `union_all_count`: COUNT of ids under UNION ALL (keeps overlaps) Overlapping ids in the seed are 1 and 2.