Join row counts with duplicates and NULLs

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Joins. About 12 minutes. Part of the Pro drill bank.

join_a and join_b each have one column, id. Both contain repeated values and one NULL. Return a single row with the number of rows each join of the two tables on id produces: inner_rows: only matching pairs left_rows: every row of join_a kept right_rows: every row of join_b kept full_rows: every row of both sides kept cross_rows: every pairing of a row from each table Compute the counts with queries. Do not type the numbers in.

Requirements

• Return the five counts as columns of one row.

Constraints

• Each table has one INTEGER column named id.

Examples

Input: join_a id 5 5 NULL 6 7 8 8 join_b id 5 NULL 6 6 8 9 Output: inner_rows | left_rows | right_rows | full_rows | cross_rows 6 | 8 | 8 | 10 | 42 Why this passes: Matched pairs: 5 (2x1), 6 (1x2), 8 (2x1) give 6. Outer joins add the unmatched 7 and NULL on the left, 9 and NULL on the right.

Input: join_a id 1 1 2 3 join_b id 1 2 5 Output: inner_rows | left_rows | right_rows | full_rows | cross_rows 3 | 4 | 4 | 5 | 12 Why this passes: No NULLs and no repeats on the right: inner 3 (1, 1, 2), left keeps all 4 left rows, right keeps all 3 right rows, full adds 3 and 5, cross is 4 x 3.

Topics: lakebench, sql, joins, null, duplicates.

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