Join to Find Inactive Users | Dataford Interview Questions

Your question is Join to Find Inactive Users. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Guidewire wants to identify active users of InsuranceSuite who have not interacted with the PolicyCenter Dashboard in more than 30 days. The reference date is 2025-03-01 00:00:00 UTC.

Write a PostgreSQL query that uses a LEFT JOIN and a common table expression to return these users.

Requirements

Consider only users whose account_status is active.
Find each user's most recent interaction with the PolicyCenter Dashboard.
Include users who have never interacted with that feature.
Return the user ID, user name, last interaction timestamp, and whole days inactive. Treat exactly 30 days of inactivity as not qualifying, and sort by user_id.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique InsuranceSuite user identifier
user_name	VARCHAR(100)	User display name
account_status	VARCHAR(20)	Current account state

feature_interactions

Column	Type	Description
interaction_idPK	INTEGER	Unique interaction identifier
user_id	INTEGER	References users.user_id
feature_name	VARCHAR(100)	Guidewire feature that was used
interacted_at	TIMESTAMPTZ	UTC interaction timestamp