Database Schema Design | Dataford Interview Questions

What are the key considerations when designing a database schema for a new application?

Treat this as a PostgreSQL schema-design exercise. Using the supplied relational tables, write a query that summarizes relationship coverage for every user, including users without applications or events.

Output

One row per user with user_id, email, application_count, and latest_event_at
Include users with no related records, displaying zero or NULL where appropriate
Order by user_id ascending

Schema

app_users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
full_name	VARCHAR(150)	User display name
created_at	TIMESTAMP	Account creation timestamp

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
user_id	INT	User associated with the application
application_name	VARCHAR(150)	Application name
status	VARCHAR(30)	Current application status
submitted_at	DATE	Submission date

application_events

Column	Type	Description
event_idPK	INT	Unique event identifier
application_id	INT	Application associated with the event
event_type	VARCHAR(40)	Type of application event
event_at	TIMESTAMP	Event occurrence timestamp

Your question is Database Schema Design. Start with the requirements and the three 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