agent-pr-replay

Analyzing PR #142 from the `data-pipelines` repository (https://github.com/acme/data-pipelines/pull/142), which merged a new feature to optimize the ETL process for customer transaction data. The PR description stated: 'Add a new column to the transactions table that calculates the rolling 7-day average purchase amount per customer, partitioned by customer_id.'

**Inferred Original Prompt:**
'Write a SQL query to add a computed column to the transactions table that calculates the 7-day rolling average of purchase amounts, grouped by customer_id. The column should be named rolling_7day_avg and should be updated automatically as new transactions are inserted.'

**AI Agent Simulation (Claude Code Output):**
```sql
ALTER TABLE transactions
ADD COLUMN rolling_7day_avg DECIMAL(10,2);

-- Create a materialized view for the rolling average
CREATE MATERIALIZED VIEW customer_rolling_avg AS
SELECT 
    customer_id,
    AVG(amount) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM transactions;
```

**Human Implementation (Actual PR):**
```sql
-- Added a trigger-based solution
CREATE OR REPLACE FUNCTION update_rolling_avg()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE transactions 
    SET rolling_7day_avg = (
        SELECT AVG(amount) 
        FROM transactions t2 
        WHERE t2.customer_id = NEW.customer_id 
        AND t2.transaction_date BETWEEN NEW.transaction_date - INTERVAL '7 days' AND NEW.transaction_date
    )
    WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_rolling_avg
AFTER INSERT ON transactions
FOR EACH ROW EXECUTE FUNCTION update_rolling_avg();
```

**Key Differences:**
1. **Approach:** The AI agent proposed a materialized view, which would require periodic refreshes, while the human implemented a trigger-based solution for real-time updates.
2. **Performance:** The trigger approach ensures data consistency at write-time, while the materialized view would need to be refreshed to stay current.
3. **Complexity:** The human solution required additional PL/pgSQL expertise, which the AI agent didn't account for.

**Actionable Recommendations:**
1. **Prompt Refinement:** Future prompts should specify whether real-time updates are required (e.g., 'Ensure the rolling average is updated in real-time as new transactions are inserted').
2. **Database Constraints:** The prompt should include database-specific constraints (e.g., 'Use PostgreSQL triggers for real-time updates').
3. **Testing Requirements:** Add a requirement to test performance under high write loads, as triggers can impact insert performance.
4. **Documentation:** Include a note about the trade-offs between materialized views and triggers in the prompt to guide the agent's approach.