LinkedIn: Easy Apply Share of Applications per Job

Easy
730.

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.

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

ColumnType
job_idint
company_namevarchar
titlevarchar

Primary key: job_id.

One row per job posting.

Table: JobApplication

ColumnTypeValues
application_idint
job_idint
member_idint
methodenumeasy_apply, company_site

Primary key: application_id.

One row per application; job_id always names a posting.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

JobPosting

job_idcompany_nametitle
1Kestrel AnalyticsData Analyst
2Bluepeak SystemsSoftware Engineer
3Orbit RetailProduct Manager
4Tidewater MediaUX Designer

JobApplication

application_idjob_idmember_idmethod
1131easy_apply
2132easy_apply
3133company_site
4231company_site
5234company_site
6335easy_apply

Output

job_idapplicationseasy_apply_pct
1366.7
220
31100
40NULL
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
job_idint
company_namevarchar
titlevarchar

Primary key: job_id.

One row per job posting.

Table: JobApplication

ColumnType
application_idint
job_idint
member_idint
methodenum(easy_apply, company_site)

Primary key: application_id.

One row per application; job_id always names a posting.

Examples

Example 1

JobPosting

job_idcompany_nametitle
1Kestrel AnalyticsData Analyst
2Bluepeak SystemsSoftware Engineer
3Orbit RetailProduct Manager
4Tidewater MediaUX Designer

JobApplication

application_idjob_idmember_idmethod
1131easy_apply
2132easy_apply
3133company_site
4231company_site
5234company_site
6335easy_apply

Output

job_idapplicationseasy_apply_pct
1366.7
220
31100
40NULL

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_id

Another 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 →