SQL indexing fundamentals | Dataford Interview Questions

How does SQL indexing work and when does an index help or hurt?

Asked in the Round 1 - technical screen stage. Listed as 'SQL indexing'. For the hands-on portion, use the supplied tables to return qualifying events and identify indexes that could support the query.

Output

One row per qualifying event, with event_id, file_name, event_type, occurred_at, and user_name
Include events from 2026-01-01 onward only when the associated user is active
Order by occurred_at descending, then event_id ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
is_active	BOOLEAN	Whether the user is currently active

file_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
file_name	VARCHAR(150)	Name of the affected file
event_type	VARCHAR(30)	Type of file event
occurred_at	TIMESTAMP	Time when the event occurred

Your question is SQL indexing fundamentals. Start with the requirements and the two tables in the Question tab.

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

Problem