Your question is Merge Client Revenue with Lookup Defaults. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given a client list and a historical revenue table. Write a PostgreSQL query that returns every client from the client list, attaches the most recent revenue found in the revenue history, and uses a lookup default when no revenue exists. If a client has no matching revenue row, show 0 as the revenue amount and Unknown as the revenue source.
| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Primary key for the client list |
| client_name | VARCHAR(100) | Client display name |
| segment | VARCHAR(50) | Client segment |
| Column | Type | Description |
|---|---|---|
| revenue_idPK | INT | Primary key for revenue rows |
| client_id | INT | Client identifier from the lookup table |
| revenue_month | DATE | Month the revenue was recorded |
| revenue_amount | NUMERIC(12,2) | Revenue amount for that month |
| revenue_source | VARCHAR(50) | Source system or feed name |