Given a table of user transactions and a table of user logins, write a query to identify the top 10% of users by spend who have logged in within the last 30 days.
Use CURRENT_DATE as the reference date. Treat a user's spend as the sum of their transaction amounts, and rank all users with transactions before selecting users in the top decile.
user_id, total_spend, and last_login_at.total_spend descending, then user_id ascending.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| user_id | INT | User who made the transaction |
| amount | NUMERIC(12,2) | Transaction amount included in user spend |
| transaction_at | TIMESTAMP | Timestamp when the transaction occurred |
| Column | Type | Description |
|---|---|---|
| login_idPK | INT | Unique login event identifier |
| user_id | INT | User associated with the login |
| login_at | TIMESTAMP | Timestamp when the user logged in |