Identify High-Ticket Inactive Customers

MediumSQL · PostgreSQL

Problem

At Autodesk, the support team wants to find customers who generated a high number of support tickets in May 2024 but did not log into Autodesk Fusion during that same month.

Write a SQL query to return customers who meet these conditions.

Requirements

Count support tickets submitted in May 2024 for each customer.
Keep only customers with more than 3 tickets in that month.
Exclude any customer who had an Autodesk Fusion login in May 2024.
Return the customer's most recent Autodesk Fusion login before 2024-05-01. If none exists, return NULL.
Order the result by ticket_count_past_month descending, then last_login_at ascending with NULLS FIRST.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
account_status	VARCHAR(30)	Current account status

support_tickets

Column	Type	Description
ticket_idPK	INT	Unique support ticket identifier
customer_id	INT	Customer who submitted the ticket
product_name	VARCHAR(100)	Product associated with the ticket
submitted_at	TIMESTAMP	Ticket submission timestamp
issue_type	VARCHAR(50)	Support issue category

login_events

Column	Type	Description
login_idPK	INT	Unique login event identifier
customer_id	INT	Customer who logged in
product_name	VARCHAR(100)	Product used during login
login_at	TIMESTAMP	Login timestamp
device_type	VARCHAR(30)	Device used for login

Tablescustomerssupport_ticketslogin_events