Top Job Listings by Engagement | Dataford Interview Questions

Glassdoor wants to identify the strongest-performing active job listings on its job search surface. Engagement is measured across views, clicks, saves, and applications during January 2025.

Write a PostgreSQL query to return the top 5 active Glassdoor job listings by weighted engagement score.

Requirements

Include only active listings and engagement events from 2025-01-01 through 2025-01-31, inclusive.
Calculate engagement score using weights of 1 for a view, 2 for a click, 3 for a save, and 5 for an application.
Return each listing's title, company, location, total event count, and engagement score. Include active listings with no qualifying events, then sort by score descending, event count descending, and listing ID ascending to break ties.

Schema

job_listings

Column	Type	Description
listing_idPK	INT	Unique job listing identifier
title	VARCHAR(150)	Displayed job title
company_name	VARCHAR(150)	Employer associated with the listing
location	VARCHAR(100)	Job location, which may be undisclosed
is_active	BOOLEAN	Whether the listing is active

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
listing_id	INT	Job listing associated with the event
user_id	INT	User who generated the event
event_type	VARCHAR(20)	Type of engagement action
event_at	TIMESTAMP	Timestamp when the action occurred

Your question is Top Job Listings by Engagement. 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