Task
You are given Discord-style moderation data and need to identify which servers have the highest abuse-report burden relative to their active user base. Write a PostgreSQL query that returns each server's abuse report count, active user count, ban count, and the ratio of abuse reports to active users. Treat a server as active based on the active_users value in the server snapshot table, and include only servers with at least 100 active users. Order the results by the ratio from highest to lowest.
Schema
discord_servers
| column | type | description |
|---|
| server_id | INT | Unique server ID |
| server_name | VARCHAR(100) | Server name |
| active_users | INT | Current active users in the server |
| snapshot_date | DATE | Date of the activity snapshot |
user_reports
| column | type | description |
|---|
| report_id | INT | Unique report ID |
| server_id | INT | Server where the report was filed |
| report_type | VARCHAR(50) | Type of report |
| reported_at | DATE | Date of the report |
server_bans
| column | type | description |
|---|
| ban_id | INT | Unique ban ID |
| server_id | INT | Server where the ban occurred |
| banned_user_id | INT | User who was banned |
| banned_at | DATE | Date of the ban |
Sample data
| server_id | server_name | active_users | snapshot_date |
|---|
| 103 | Study Hall | 120 | 2024-06-30 |
| 101 | Pixel Raiders | 500 | 2024-06-30 |
| 106 | Night Shift | 1000 | 2024-06-30 |
| report_id | server_id | report_type | reported_at |
|---|
| 1 | 101 | abuse | 2024-06-02 |
| 2 | 101 | spam | 2024-06-03 |
| 5 | 103 | abuse | 2024-06-05 |
Expected output
| server_id | server_name | abuse_reports | active_users | ban_count | report_to_active_ratio |
|---|
| 103 | Study Hall | 3 | 120 | 2 | 0.0250 |
| 101 | Pixel Raiders | 4 | 500 | 3 | 0.0080 |
| 106 | Night Shift | 5 | 1000 | 0 | 0.0050 |
