MVC API on Table Data | Dataford Interview Questions

Create an MVC API call on table data using HTTP routes with on-click events.

Asked in the Round 1 HackerRank stage. For the SQL portion, prepare the query supporting a route that returns active application pages and their January 2025 click activity.

Output

One row per active page, including pages with no matching clicks.
Columns: page_id, page_name, click_count, and last_clicked_at.
Sort by click_count descending, then page_id ascending.

Schema

pages

Column	Type	Description
page_idPK	INT	Unique application page identifier
page_name	VARCHAR(100)	Display name of the page
is_active	BOOLEAN	Whether the page is currently active

click_events

Column	Type	Description
event_idPK	INT	Unique click event identifier
page_id	INT	Page associated with the click, when available
event_type	VARCHAR(30)	Type of recorded interaction
clicked_at	TIMESTAMP	Timestamp when the interaction occurred

Your question is MVC API on Table Data. 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