Task
You are given Discord-style moderation data and asked to identify which servers have the highest ratio of abuse reports to active users. Write a PostgreSQL query that returns each server's server_id, total abuse reports, active user count, total bans, and the abuse report ratio defined as abuse_reports / active_users. Treat a user as active if they have is_active = true. Only include servers with at least 2 active users, and sort the results by ratio descending, then by server_id ascending.
Schema
server_members
| column | type | description |
|---|
| member_id | INT | Unique membership row |
| server_id | INT | Discord server ID |
| user_id | INT | User ID |
| is_active | BOOLEAN | Whether the member is currently active in the server |
| joined_at | DATE | Join date |
user_reports
| column | type | description |
|---|
| report_id | INT | Unique abuse report |
| server_id | INT | Server where the report was filed |
| reporter_user_id | INT | User who submitted the report |
| reported_user_id | INT | User being reported |
| report_type | VARCHAR(50) | Report category |
| created_at | DATE | Report date |
server_bans
| column | type | description |
|---|
| ban_id | INT | Unique ban event |
| server_id | INT | Server where the ban occurred |
| banned_user_id | INT | Banned user |
| banned_at | DATE | Ban date |
Sample data
Representative rows:
| server_id | user_id | is_active |
|---|
| 101 | 1001 | true |
| 101 | 1002 | true |
| 102 | 1005 | false |
| 104 | 1010 | true |
| server_id | report_type | reporter_user_id | reported_user_id |
|---|
| 101 | abuse | 1001 | 1002 |
| 101 | spam | 1002 | 1003 |
| 104 | abuse | 1010 | 1011 |
| 106 | abuse | 1015 | 1016 |
Expected output
| server_id | abuse_reports | active_users | total_bans | abuse_report_ratio |
|---|
| 106 | 3 | 2 | 2 | 1.5000 |
| 104 | 2 | 2 | 1 | 1.0000 |
| 101 | 3 | 3 | 2 | 1.0000 |
| 103 | 1 | 2 | 0 | 0.5000 |
