LinkedIn: Easy Apply Share of Applications per Job
Product managers on Jobs compare how many candidates use Easy Apply instead of the employer's own site.
- Difficulty: Easy
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #730
Problem statement
Product managers on Jobs compare how many candidates use Easy Apply instead of the employer's own site.
For every job posting, return job_id, applications (its number of applications) and easy_apply_pct, the percentage of those applications made with easy_apply, rounded to one decimal place. A job with no applications shows applications = 0 and easy_apply_pct = NULL. Return the rows in any order.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: JobPosting
| Column | Type |
|---|---|
| job_id | int |
| company_name | varchar |
| title | varchar |
Primary key: job_id.
One row per job posting.
Table: JobApplication
| Column | Type |
|---|---|
| application_id | int |
| job_id | int |
| member_id | int |
| method | enum(easy_apply, company_site) |
Primary key: application_id.
One row per application; job_id always names a posting.
Examples
Example 1
JobPosting
| job_id | company_name | title |
|---|---|---|
| 1 | Kestrel Analytics | Data Analyst |
| 2 | Bluepeak Systems | Software Engineer |
| 3 | Orbit Retail | Product Manager |
| 4 | Tidewater Media | UX Designer |
JobApplication
| application_id | job_id | member_id | method |
|---|---|---|---|
| 1 | 1 | 31 | easy_apply |
| 2 | 1 | 32 | easy_apply |
| 3 | 1 | 33 | company_site |
| 4 | 2 | 31 | company_site |
| 5 | 2 | 34 | company_site |
| 6 | 3 | 35 | easy_apply |
Output
| job_id | applications | easy_apply_pct |
|---|---|---|
| 1 | 3 | 66.7 |
| 2 | 2 | 0 |
| 3 | 1 | 100 |
| 4 | 0 | NULL |
How to solve LinkedIn: Easy Apply Share of Applications per Job
Start from JobPosting and LEFT JOIN the applications so that every job forms a group. For a job nobody applied to, the join produces one row of NULLs: COUNT(a.application_id) counts it as 0 (while COUNT(*) would wrongly say 1), and the share is undefined — NULLIF(…, 0) turns the zero denominator into NULL, and any arithmetic with NULL is NULL. MySQL would also return NULL for a division by zero, but writing it out makes the intent clear and does not depend on the SQL mode.
The share itself is a conditional count over the total, times 100, rounded last. The alternative aggregates the applications first in a derived table — AVG(method = 'easy_apply') is the fraction of rows where the comparison is 1 — and attaches it to the postings with a LEFT JOIN, where a job without a match gets NULL for the share and COALESCE turns its count into 0. Both read each table once.
Reference solution (MySQL)
SELECT j.job_id,
COUNT(a.application_id) AS applications,
ROUND(100 * SUM(CASE WHEN a.method = 'easy_apply' THEN 1 ELSE 0 END) / NULLIF(COUNT(a.application_id), 0), 1) AS easy_apply_pct
FROM JobPosting j
LEFT JOIN JobApplication a ON a.job_id = j.job_id
GROUP BY j.job_idAnother way
SELECT j.job_id, COALESCE(t.applications, 0) AS applications, t.easy_apply_pct
FROM JobPosting j
LEFT JOIN (SELECT job_id, COUNT(*) AS applications, ROUND(100 * AVG(method = 'easy_apply'), 1) AS easy_apply_pct
FROM JobApplication GROUP BY job_id) t ON t.job_id = j.job_id← LinkedIn: Learning Course Completions per Category in Q1 2025 · LinkedIn: Connection Count of Every Member →