Your question is Clean Duplicate Transactions and Null Costs. Start with the requirements and the one table 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 transaction-level cost data that may contain duplicate records and missing cost values. Write a PostgreSQL query that returns one reporting row per customer and month, using the latest version of each transaction, removing duplicate transaction records, and replacing null cost values with the customer’s average non-null cost for that month. If a customer-month has no non-null cost at all, use 0.
| Column | Type | Description |
|---|---|---|
| transaction_id | VARCHAR(20) | Business transaction identifier |
| customer_name | VARCHAR(100) | Customer name |
| transaction_date | DATE | Date the transaction occurred |
| cost | DECIMAL(10,2) | Transaction cost, may be missing |
| updated_at | TIMESTAMP | Timestamp of the latest record version |