How do you approach SQL: Reconcile Two Sources (FULL OUTER JOIN)?

FULL OUTER JOIN keeps rows from BOTH sides, padding the missing side with NULLs.

What are common mistakes on SQL: Reconcile Two Sources (FULL OUTER JOIN)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Reconcile Two Sources (FULL OUTER JOIN)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. You have two billing systems exporting daily revenue per account_id: ``` SystemA(account_id, revenue_a) SystemB(account_id, revenue_b) ``` Produce a reconciliation report listing every account_id that appears in **either** source, with both revenues (use 0 if missing on a side) and the absolute mismatch. Sort by account_id ascending. **For the auto-grader:** return objects shaped `{ account_id, revenue_a, revenue_b, diff }` where `diff = |revenue_a - revenue_b|`.

Example 1

Input: a = [{account_id:1,revenue_a:100},{account_id:2,revenue_a:200}], b = [{account_id:2,revenue_b:200},{account_id:3,revenue_b:300}]

Output: [{account_id:1,revenue_a:100,revenue_b:0,diff:100},{account_id:2,revenue_a:200,revenue_b:200,diff:0},{account_id:3,revenue_a:0,revenue_b:300,diff:300}]

Constraints

- account_id is a positive integer. - Revenues are non-negative integers, may exceed 32-bit range. - Up to 10^4 rows per source. account_id is unique per source.

SQL: Reconcile Two Sources (FULL OUTER JOIN): approach, complexity and pitfalls

A medium sql problem, graded against 4 test cases (2 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 3-step hint ladder

FULL OUTER JOIN keeps rows from BOTH sides, padding the missing side with NULLs.
Use COALESCE to default each revenue to 0 when NULL.
MySQL has no FULL OUTER JOIN - emulate with UNION of LEFT and RIGHT joins.

The optimal approach

Show the approach: Canonical SQL (Postgres / SQL Server / Oracle):

```sql

SELECT

COALESCE(a.account_id, b.account_id) AS account_id,

COALESCE(a.revenue_a, 0) AS revenue_a,

COALESCE(b.revenue_b, 0) AS revenue_b,

ABS(COALESCE(a.revenue_a, 0) - COALESCE(b.revenue_b, 0)) AS diff

FROM SystemA a

FULL OUTER JOIN SystemB b ON a.account_id = b.account_id

ORDER BY account_id;

```

`COALESCE` substitutes `0` for any side that's NULL, and the joined `account_id` is taken from whichever side has it.

MySQL workaround (no FULL JOIN):

```sql

SELECT a.account_id, a.revenue_a, COALESCE(b.revenue_b, 0) AS revenue_b ...

FROM A LEFT JOIN B ON ...

UNION

SELECT b.account_id, COALESCE(a.revenue_a, 0), b.revenue_b ...

FROM A RIGHT JOIN B ON ...

```

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.

← Back to problems

SQL: Reconcile Two Sources (FULL OUTER JOIN)

MediumDatabases

Description

Example 1

Input: a = [{account_id:1,revenue_a:100},{account_id:2,revenue_a:200}], b = [{account_id:2,revenue_b:200},{account_id:3,revenue_b:300}]

Output: [{account_id:1,revenue_a:100,revenue_b:0,diff:100},{account_id:2,revenue_a:200,revenue_b:200,diff:0},{account_id:3,revenue_a:0,revenue_b:300,diff:300}]

Constraints

- account_id is a positive integer. - Revenues are non-negative integers, may exceed 32-bit range. - Up to 10^4 rows per source. account_id is unique per source.

SQL: Reconcile Two Sources (FULL OUTER JOIN): approach, complexity and pitfalls

A medium sql problem, graded against 4 test cases (2 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 3-step hint ladder

FULL OUTER JOIN keeps rows from BOTH sides, padding the missing side with NULLs.
Use COALESCE to default each revenue to 0 when NULL.
MySQL has no FULL OUTER JOIN - emulate with UNION of LEFT and RIGHT joins.

The optimal approach

Show the approach: Canonical SQL (Postgres / SQL Server / Oracle):

```sql

SELECT

COALESCE(a.account_id, b.account_id) AS account_id,

COALESCE(a.revenue_a, 0) AS revenue_a,

COALESCE(b.revenue_b, 0) AS revenue_b,

ABS(COALESCE(a.revenue_a, 0) - COALESCE(b.revenue_b, 0)) AS diff

FROM SystemA a

FULL OUTER JOIN SystemB b ON a.account_id = b.account_id

ORDER BY account_id;

```

`COALESCE` substitutes `0` for any side that's NULL, and the joined `account_id` is taken from whichever side has it.

MySQL workaround (no FULL JOIN):

```sql

SELECT a.account_id, a.revenue_a, COALESCE(b.revenue_b, 0) AS revenue_b ...

FROM A LEFT JOIN B ON ...

UNION

SELECT b.account_id, COALESCE(a.revenue_a, 0), b.revenue_b ...

FROM A RIGHT JOIN B ON ...

```

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.