Write a SQL query to identify duplicate transactions within a specific time window across a distributed database.
For this exercise, treat transactions as duplicates when they share account, amount, currency, and transaction type, occur on different shards, and are no more than five minutes apart. Search from 2025-01-15 09:00:00+00 inclusive through 2025-01-15 10:00:00+00 exclusive.
transaction_id_1, transaction_id_2, account_id, amount, occurred_at_1, occurred_at_2, shard_name_1, shard_name_2, and difference_seconds.occurred_at_1, then transaction_id_1, then transaction_id_2.| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Unique transaction identifier |
| account_id | BIGINT | Account associated with the transaction |
| amount | NUMERIC(12,2) | Transaction amount |
| currency | VARCHAR(3) | ISO currency code |
| transaction_type | VARCHAR(30) | Transaction classification |
| occurred_at | TIMESTAMPTZ | Time the transaction occurred |
| shard_id | VARCHAR(20) | Distributed database shard containing the transaction |
| Column | Type | Description |
|---|---|---|
| shard_idPK | VARCHAR(20) | Unique shard identifier |
| shard_name | VARCHAR(50) | Human-readable shard name |
| region | VARCHAR(30) | Deployment region for the shard |