Given a transactions table, write a single SQL query that ranks each transaction within its user by amount (largest first), breaking ties by timestamp (earlier first).
Input
transactions(user_id INTEGER, ts TIMESTAMP, amount DOUBLE) — one row per transaction.
Task
Within each user, assign a rank to every transaction where rank 1 is the largest amount. When two transactions have the same amount, the one with the earlier ts receives the better (smaller) rank. Every transaction receives a distinct rank within its user (no shared ranks, no gaps).
Output
One row per transaction, columns in this exact order:
user_id (INTEGER)ts (TIMESTAMP)amount (DOUBLE)amount_rank (INTEGER)
Order by user_id, then amount_rank.
Evaluation
Your query is executed against several hidden datasets in a DuckDB sandbox. Output is compared row-for-row against the reference result with a numeric tolerance of +/-0.01.
Worked example
User 1 has transactions: 500 at 10:00, 500 at 09:00, 300 at the next day. The two 500s tie on amount, so the 09:00 one ranks 1 and the 10:00 one ranks 2; the 300 ranks 3.
Expected output:
| user_id | ts | amount | amount_rank |
|---------|---------------------|--------|-------------|
| 1 | 2024-01-01 09:00:00 | 500 | 1 |
| 1 | 2024-01-01 10:00:00 | 500 | 2 |
| 1 | 2024-01-02 08:00:00 | 300 | 3 |