LinkedIn: Applications Year over Year by Company and Quarter

Hard
743.

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

ColumnType
company_idint
company_namevarchar

Primary key: company_id.

One row per hiring customer; names are unique.

Table: JobPosting

ColumnType
job_idint
company_idint
titlevarchar

Primary key: job_id.

One row per job posting; company_id always names a company.

Table: JobApplication

ColumnType
application_idint
job_idint
applied_atdatetime

Primary key: application_id.

One row per application; job_id always names a posting.

Example

The rows must come back in the order shown.

Company

company_idcompany_name
1Kestrel Analytics
2Bluepeak Systems

JobPosting

job_idcompany_idtitle
101Data Analyst
111Product Manager
122Software Engineer

JobApplication

application_idjob_idapplied_at
1102024-02-10 10:00:00
2112024-03-31 23:50:00
3102025-01-15 09:00:00
4102025-02-20 12:00:00
5112025-03-02 18:00:00
6102024-07-04 11:00:00
7122025-04-01 00:05:00
8122023-12-30 15:00:00
9102025-11-11 11:00:00

Output

company_namequarter_noapps_2024apps_2025growth_pct
Bluepeak Systems100NULL
Bluepeak Systems201NULL
Bluepeak Systems300NULL
Bluepeak Systems400NULL
Kestrel Analytics12350
Kestrel Analytics200NULL
Kestrel Analytics310-100
Kestrel Analytics401NULL
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: 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

ColumnType
company_idint
company_namevarchar

Primary key: company_id.

One row per hiring customer; names are unique.

Table: JobPosting

ColumnType
job_idint
company_idint
titlevarchar

Primary key: job_id.

One row per job posting; company_id always names a company.

Table: JobApplication

ColumnType
application_idint
job_idint
applied_atdatetime

Primary key: application_id.

One row per application; job_id always names a posting.

Examples

Example 1

Company

company_idcompany_name
1Kestrel Analytics
2Bluepeak Systems

JobPosting

job_idcompany_idtitle
101Data Analyst
111Product Manager
122Software Engineer

JobApplication

application_idjob_idapplied_at
1102024-02-10 10:00:00
2112024-03-31 23:50:00
3102025-01-15 09:00:00
4102025-02-20 12:00:00
5112025-03-02 18:00:00
6102024-07-04 11:00:00
7122025-04-01 00:05:00
8122023-12-30 15:00:00
9102025-11-11 11:00:00

Output

company_namequarter_noapps_2024apps_2025growth_pct
Bluepeak Systems100NULL
Bluepeak Systems201NULL
Bluepeak Systems300NULL
Bluepeak Systems400NULL
Kestrel Analytics12350
Kestrel Analytics200NULL
Kestrel Analytics310-100
Kestrel Analytics401NULL

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_no

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

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