Summarize User Applications and Latest Events

MediumSQL · PostgreSQL

Problem

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

Output details

Count each application only once, even when it has multiple events.
Return application_count as 0 for users without applications.
Leave latest_event_at as NULL when no related events exist.

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

Tablesapp_usersapplicationsapplication_events