Task
You are given Qlik planning records where some financial inputs are missing. Write a PostgreSQL query that returns one row per plan record for January 2024, showing the original amount, the filled amount, the method used to fill it, and the manager name. If amount is present, keep it. If it is missing, first use the account-level default amount; if that is also missing, use 0. Also classify each row as original, account_default, or zero_fallback.
Schema
qlik_plan_records
| column | type | description |
|---|
| record_id | INT | Unique plan record ID |
| account_id | INT | Account tied to the plan record |
| manager_id | INT | Manager responsible for the record |
| plan_month | DATE | Month of the planning record |
| amount | DECIMAL(12,2) | Planned amount; may be NULL |
qlik_accounts
| column | type | description |
|---|
| account_id | INT | Unique account ID |
| account_name | VARCHAR(100) | Account name |
| default_amount | DECIMAL(12,2) | Default amount used when plan amount is missing |
qlik_managers
| column | type | description |
|---|
| manager_id | INT | Unique manager ID |
| manager_name | VARCHAR(100) | Manager name |
Sample data
| record_id | account_id | manager_id | plan_month | amount |
|---|
| 101 | 1 | 10 | 2024-01-01 | NULL |
| 102 | 2 | 11 | 2024-01-01 | 1250.00 |
| 103 | 3 | 12 | 2024-01-01 | NULL |
Expected output
| record_id | account_name | manager_name | original_amount | filled_amount | fill_method |
|---|
| 101 | Enterprise North | Maya Chen | NULL | 1000.00 | account_default |
| 102 | Mid-Market West | Daniel Reed | 1250.00 | 1250.00 | original |
| 103 | SMB Central | Priya Nair | NULL | 0.00 | zero_fallback |
