LinkedIn: Applications Year over Year by Company and Quarter
Account managers for LinkedIn's hiring customers bring a quarter-by-quarter comparison of how many applications each customer's jobs received this year…
- Difficulty: Hard
- Topics: Joins, Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #743
Problem statement
Account managers for LinkedIn's hiring customers bring a quarter-by-quarter comparison of how many applications each customer's jobs received this year against last year — with every quarter on the page, even an empty one.
For every company and every quarter 1 to 4, return company_name, quarter_no, apps_2024 and apps_2025 (applications to the company's postings made in that quarter of 2024 and of 2025, 0 when none) and growth_pct: (apps_2025 − apps_2024) ÷ apps_2024 × 100, rounded to one decimal place, or NULL when apps_2024 is 0. Applications outside 2024 and 2025 are ignored. Order the rows by company_name, then quarter_no.
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: Company
| Column | Type |
|---|---|
| company_id | int |
| company_name | varchar |
Primary key: company_id.
One row per hiring customer; names are unique.
Table: JobPosting
| Column | Type |
|---|---|
| job_id | int |
| company_id | int |
| title | varchar |
Primary key: job_id.
One row per job posting; company_id always names a company.
Table: JobApplication
| Column | Type |
|---|---|
| application_id | int |
| job_id | int |
| applied_at | datetime |
Primary key: application_id.
One row per application; job_id always names a posting.
Examples
Example 1
Company
| company_id | company_name |
|---|---|
| 1 | Kestrel Analytics |
| 2 | Bluepeak Systems |
JobPosting
| job_id | company_id | title |
|---|---|---|
| 10 | 1 | Data Analyst |
| 11 | 1 | Product Manager |
| 12 | 2 | Software Engineer |
JobApplication
| application_id | job_id | applied_at |
|---|---|---|
| 1 | 10 | 2024-02-10 10:00:00 |
| 2 | 11 | 2024-03-31 23:50:00 |
| 3 | 10 | 2025-01-15 09:00:00 |
| 4 | 10 | 2025-02-20 12:00:00 |
| 5 | 11 | 2025-03-02 18:00:00 |
| 6 | 10 | 2024-07-04 11:00:00 |
| 7 | 12 | 2025-04-01 00:05:00 |
| 8 | 12 | 2023-12-30 15:00:00 |
| 9 | 10 | 2025-11-11 11:00:00 |
Output
| company_name | quarter_no | apps_2024 | apps_2025 | growth_pct |
|---|---|---|---|---|
| Bluepeak Systems | 1 | 0 | 0 | NULL |
| Bluepeak Systems | 2 | 0 | 1 | NULL |
| Bluepeak Systems | 3 | 0 | 0 | NULL |
| Bluepeak Systems | 4 | 0 | 0 | NULL |
| Kestrel Analytics | 1 | 2 | 3 | 50 |
| Kestrel Analytics | 2 | 0 | 0 | NULL |
| Kestrel Analytics | 3 | 1 | 0 | -100 |
| Kestrel Analytics | 4 | 0 | 1 | NULL |
How to solve LinkedIn: Applications Year over Year by Company and Quarter
The report must show a row for every company and every quarter, even when a company had no applications at all in a quarter. Rows that do not exist in the data cannot be produced by grouping it, so the query builds the grid first: a four-row quarters table from UNION ALL, CROSS JOINed with Company. Then the facts are attached to the grid with a LEFT JOIN.
The facts are counted once per company and quarter, with conditional aggregation placing each application in its year's column — that turns the year from rows into the two columns being compared. The WHERE drops applications outside 2024–2025 (in the example, the one from December 2023). Because applied_at has a time, the bounds are half-open, and QUARTER() puts 23:50 on March 31 in quarter 1.
A cell with no applications comes through the LEFT JOIN as NULL and COALESCE shows 0. Growth divides by last year's count, and growth from nothing is undefined, so NULLIF(k.y24, 0) makes the division NULL instead of an error or a misleading number; a fall from 2 to 0 is −100.0.
The first alternative fills each cell with two correlated counts — simple, but eight probes per company. The second generates the quarters with a recursive CTE and LEFT JOINs postings and applications straight onto the grid, counting with COUNT(CASE …), where the NULLs of an empty cell add nothing.
Reference solution (MySQL)
WITH quarters AS (
SELECT 1 AS quarter_no UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
), counts AS (
SELECT j.company_id, QUARTER(a.applied_at) AS q,
SUM(CASE WHEN YEAR(a.applied_at) = 2024 THEN 1 ELSE 0 END) AS y24,
SUM(CASE WHEN YEAR(a.applied_at) = 2025 THEN 1 ELSE 0 END) AS y25
FROM JobApplication a
JOIN JobPosting j ON j.job_id = a.job_id
WHERE a.applied_at >= '2024-01-01' AND a.applied_at < '2026-01-01'
GROUP BY j.company_id, QUARTER(a.applied_at)
)
SELECT c.company_name, qs.quarter_no,
COALESCE(k.y24, 0) AS apps_2024,
COALESCE(k.y25, 0) AS apps_2025,
ROUND(100 * (COALESCE(k.y25, 0) - k.y24) / NULLIF(k.y24, 0), 1) AS growth_pct
FROM Company c
CROSS JOIN quarters qs
LEFT JOIN counts k ON k.company_id = c.company_id AND k.q = qs.quarter_no
ORDER BY c.company_name, qs.quarter_noAnother way
SELECT g.company_name, g.quarter_no, g.apps_2024, g.apps_2025,
IF(g.apps_2024 = 0, NULL, ROUND(100 * (g.apps_2025 - g.apps_2024) / g.apps_2024, 1)) AS growth_pct
FROM (
SELECT c.company_name, qs.quarter_no,
(SELECT COUNT(*) FROM JobApplication a JOIN JobPosting j ON j.job_id = a.job_id
WHERE j.company_id = c.company_id AND YEAR(a.applied_at) = 2024 AND QUARTER(a.applied_at) = qs.quarter_no) AS apps_2024,
(SELECT COUNT(*) FROM JobApplication a JOIN JobPosting j ON j.job_id = a.job_id
WHERE j.company_id = c.company_id AND YEAR(a.applied_at) = 2025 AND QUARTER(a.applied_at) = qs.quarter_no) AS apps_2025
FROM Company c
CROSS JOIN (SELECT 1 AS quarter_no UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) qs
) g
ORDER BY g.company_name, g.quarter_noAnother way
WITH RECURSIVE qs AS (SELECT 1 AS quarter_no UNION ALL SELECT quarter_no + 1 FROM qs WHERE quarter_no < 4)
SELECT c.company_name, qs.quarter_no,
COUNT(CASE WHEN a.applied_at >= '2024-01-01' AND a.applied_at < '2025-01-01' THEN 1 END) AS apps_2024,
COUNT(CASE WHEN a.applied_at >= '2025-01-01' AND a.applied_at < '2026-01-01' THEN 1 END) AS apps_2025,
ROUND(100 * (COUNT(CASE WHEN a.applied_at >= '2025-01-01' AND a.applied_at < '2026-01-01' THEN 1 END)
- COUNT(CASE WHEN a.applied_at >= '2024-01-01' AND a.applied_at < '2025-01-01' THEN 1 END))
/ NULLIF(COUNT(CASE WHEN a.applied_at >= '2024-01-01' AND a.applied_at < '2025-01-01' THEN 1 END), 0), 1) AS growth_pct
FROM Company c
CROSS JOIN qs
LEFT JOIN JobPosting j ON j.company_id = c.company_id
LEFT JOIN JobApplication a ON a.job_id = j.job_id AND QUARTER(a.applied_at) = qs.quarter_no
GROUP BY c.company_id, c.company_name, qs.quarter_no
ORDER BY c.company_name, qs.quarter_no← LinkedIn: Followers Across Each Company's Affiliated Pages · LinkedIn: Merge Overlapping Premium Periods Into Spans →