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.

The same 2 tables give different row counts depending on how you join them, and many bugs come from not knowing the count in advance. In this problem you will measure it. join_a and join_b each have 1 column, id. Both have repeated values and one NULL. Return 1 row with the number of rows produced by each kind of join on id: 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 kept) and cross_rows (every pairing of 1 row from each table). Calculate the counts with queries. Do not type the numbers in.

Requirements

• Return exactly 1 row with 5 columns.
• Each count comes from a real join query, using COUNT(*).
• Use INNER, LEFT, RIGHT, FULL OUTER and CROSS joins for the 5 columns.
• Join on join_a.id = join_b.id for the first four. The cross join has no condition.

Constraints

• Each table has one INTEGER column named id.
• Put each count in its own subquery, for example (SELECT COUNT(*) FROM join_a a INNER JOIN join_b b ON a.id = b.id) AS inner_rows.
• Return only these 5 columns, in this order: inner_rows, left_rows, right_rows, full_rows, cross_rows.

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