Create a Database Management System

HardSQL · PostgreSQL

Problem

Create a database management system.

Implement the PostgreSQL data model and write a query that produces an administrative summary for every managed database. Include databases with no members or audit events, and treat revoked memberships as inactive.

Output

One row per managed database with database_name, owner_name, active_member_count, audit_event_count, latest_event_type, and latest_event_at.
Include databases without an owner, members, or events using NULL or zero values as appropriate.
Order by database_name ascending.

Schema

managed_databases

Column	Type	Description
database_idPK	INT	Unique managed database identifier
database_name	VARCHAR(100)	Display name of the managed database
owner_user_id	INT	User responsible for the database
created_at	TIMESTAMP	Database creation timestamp

database_users

Column	Type	Description
user_idPK	INT	Unique user identifier
display_name	VARCHAR(100)	User display name
email	VARCHAR(255)	User email address

database_memberships

Column	Type	Description
membership_idPK	INT	Unique membership identifier
database_id	INT	Managed database identifier
user_id	INT	Member user identifier
role	VARCHAR(30)	Membership role
granted_at	TIMESTAMP	Membership grant timestamp
revoked_at	TIMESTAMP	Membership revocation timestamp

audit_events

Column	Type	Description
event_idPK	INT	Unique audit event identifier
database_id	INT	Database associated with the event
user_id	INT	User who caused the event
event_type	VARCHAR(40)	Type of audited activity
event_at	TIMESTAMP	Event occurrence timestamp

Tablesmanaged_databasesdatabase_usersdatabase_membershipsaudit_events