You need to clean inconsistent PSCU transaction records and produce a category-level summary. The dataset includes duplicate loads, inconsistent merchant text, mixed status labels, inactive members, and missing references. Return one row per merchant category with valid posted transaction count, distinct active member count, and total posted amount.
| Column | Type | Description |
|---|---|---|
| member_id | ||
| member_name | ||
| member_status |
| Column | Type | Description |
|---|---|---|
| merchant_id | ||
| merchant_name | ||
| merchant_category |
| Column | Type | Description |
|---|---|---|
| txn_id | ||
| member_id | ||
| merchant_name_raw | ||
| txn_date | ||
| amount | ||
| txn_status | ||
| load_ts |
| merchant_category | valid_posted_txn_count | active_member_count | total_posted_amount |
|---|---|---|---|
| Digital Services | 1 | 1 | 15.99 |
| Grocery | 2 | 2 | 95.10 |
| Retail | 2 | 2 | 145.50 |