Task
You are given Autodesk customer support and product usage data. Write a PostgreSQL query to find customers who submitted more than three support tickets in the past month but did not log into Autodesk Fusion during that same period. Return the customer ID, customer name, number of tickets in the past month, and the customer's most recent login timestamp if one exists. Order the result by ticket count descending, then customer ID.
Assume the query is run as of 2024-06-01, so the past month means 2024-05-01 through 2024-05-31 inclusive.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| account_status | VARCHAR(20) | Current account status |
support_tickets
| column | type | description |
|---|
| ticket_id | INT | Unique support ticket identifier |
| customer_id | INT | Customer who submitted the ticket |
| product_name | VARCHAR(100) | Autodesk product tied to the ticket |
| submitted_at | TIMESTAMP | Ticket submission time |
| issue_type | VARCHAR(50) | Ticket category |
login_events
| column | type | description |
|---|
| login_id | INT | Unique login event identifier |
| customer_id | INT | Customer who logged in |
| product_name | VARCHAR(100) | Autodesk product used |
| login_at | TIMESTAMP | Login timestamp |
| device_type | VARCHAR(30) | Device used for login |
Sample data
customers
| customer_id | customer_name | account_status |
|---|
| 101 | Amina Patel | active |
| 102 | Brian Lee | active |
| 103 | Carla Gomez | suspended |
support_tickets
| ticket_id | customer_id | product_name | submitted_at | issue_type |
|---|
| 2001 | 101 | Autodesk Fusion | 2024-05-03 09:15:00 | login_issue |
| 2002 | 101 | Autodesk Fusion | 2024-05-10 14:30:00 | crash |
| 2003 | 101 | Autodesk Fusion | 2024-05-18 11:00:00 | billing |
login_events
| login_id | customer_id | product_name | login_at | device_type |
|---|
| 3001 | 102 | Autodesk Fusion | 2024-05-12 08:00:00 | desktop |
| 3002 | 103 | Autodesk Fusion | 2024-04-20 17:45:00 | web |
Expected output
| customer_id | customer_name | ticket_count_past_month | last_login_at |
|---|
| 101 | Amina Patel | 4 | 2024-04-28 09:00:00 |
| 106 | Farah Khan | 5 | NULL |
