Task
You are asked to write a PostgreSQL query that produces a customer-facing summary report from Cherre-style account data. Combine account records, linked properties, and monthly sync activity to return one row per account for January 2024. The report should show the account name, total linked properties, number of successful syncs in January, total records processed in January, and a status label of Healthy when the account had at least 2 successful syncs and more than 1000 records processed; otherwise label it Needs Attention.
Use all accounts in the output, even if they had no January sync activity.
Schema
| Table | Column | Type | Description |
|---|
| accounts | account_id | INT | Account identifier |
| accounts | account_name | VARCHAR(100) | Account name |
| accounts | customer_tier | VARCHAR(20) | Tier such as Enterprise or Growth |
| properties | property_id | INT | Property identifier |
| properties | account_id | INT | Owning account |
| properties | property_type | VARCHAR(30) | Property type |
| sync_runs | sync_id | INT | Sync run identifier |
| sync_runs | account_id | INT | Synced account |
| sync_runs | sync_date | DATE | Date of sync |
| sync_runs | records_processed | INT | Records processed in the run |
| sync_runs | sync_status | VARCHAR(20) | Sync result |
Sample data
| account_id | account_name | customer_tier |
|---|
| 1 | Atlas Capital | Enterprise |
| 2 | Beacon Realty | Growth |
| property_id | account_id | property_type |
|---|
| 101 | 1 | Office |
| 102 | 1 | Multifamily |
| sync_id | account_id | sync_date | records_processed | sync_status |
|---|
| 1001 | 1 | 2024-01-05 | 700 | success |
| 1002 | 1 | 2024-01-20 | 450 | success |
Expected output
| account_name | total_properties | successful_syncs_jan | total_records_jan | report_status |
|---|
| Atlas Capital | 2 | 2 | 1150 | Healthy |
| Beacon Realty | 1 | 1 | 300 | Needs Attention |
