SQL Last 20 Rows Retrieval | Dataford Interview Questions

Your question is SQL Last 20 Rows Retrieval. Start with the requirements and the three 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

Ascendion's delivery analytics team needs a report showing the most recent activity records across its project portfolio. Write a PostgreSQL query that returns the latest 20 activity rows, preserving activities even when employee or project details are unavailable.

Requirements

Use a CTE to identify the latest 20 rows from activity_events.
Sort by occurred_at descending, using event_id descending as a deterministic tie-breaker.
Use LEFT JOIN to include activity rows with missing employee or project references.
Return the event ID, timestamp, event type, employee name, and project name in newest-first order.

The sample contains fewer than 20 activity rows, so the query should return every available row while still using LIMIT 20.

Schema

activity_events

Column	Type	Description
event_idPK	INTEGER	Unique activity identifier
occurred_at	TIMESTAMP	Time the activity occurred
event_type	VARCHAR(40)	Activity classification
employee_id	INTEGER	Optional reference to the employee responsible
project_id	INTEGER	Optional reference to the related project

employees

Column	Type	Description
employee_idPK	INTEGER	Employee identifier
employee_name	VARCHAR(120)	Employee display name
department	VARCHAR(80)	Employee department

projects

Column	Type	Description
project_idPK	INTEGER	Project identifier
project_name	VARCHAR(150)	Project display name
client_name	VARCHAR(120)	Client organization