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.

Loading editor...