LinkedIn: Members Whose Profile Views Doubled in May

Medium
733.

LinkedIn: Members Whose Profile Views Doubled in May

The "Who viewed your profile" team wants to celebrate members whose profile is suddenly getting noticed.

For each viewed member, count the views their profile received in April 2025 and in May 2025. Return member_id (the viewed member), april_views and may_views for members with at least one April view whose May views are at least double their April views. Order the rows by member_id.

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

ColumnType
view_idint
viewer_idint
viewed_idint
viewed_atdatetime

Primary key: view_id.

One row per profile view: viewer_id looked at viewed_id's profile. Every view counts, including repeat views by one viewer.

Example

The rows must come back in the order shown.

ProfileView

view_idviewer_idviewed_idviewed_at
12012025-04-03 10:00:00
22112025-05-07 12:30:00
32212025-05-31 23:59:00
42322025-04-10 09:00:00
52422025-04-30 23:30:00
62522025-05-01 00:10:00
72632025-05-12 08:00:00
82732025-05-13 08:00:00
92842025-03-31 22:00:00
102842025-04-15 11:00:00
3 more rows

Output

member_idapril_viewsmay_views
112
412
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: Members Whose Profile Views Doubled in May

The "Who viewed your profile" team wants to celebrate members whose profile is suddenly getting noticed.

  • Difficulty: Medium
  • Topics: Aggregation, Conditional Logic, Dates
  • Dialect: MySQL
  • Problem: #733

Problem statement

The "Who viewed your profile" team wants to celebrate members whose profile is suddenly getting noticed.

For each viewed member, count the views their profile received in April 2025 and in May 2025. Return member_id (the viewed member), april_views and may_views for members with at least one April view whose May views are at least double their April views. Order the rows by member_id.

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

ColumnType
view_idint
viewer_idint
viewed_idint
viewed_atdatetime

Primary key: view_id.

One row per profile view: viewer_id looked at viewed_id's profile. Every view counts, including repeat views by one viewer.

Examples

Example 1

ProfileView

view_idviewer_idviewed_idviewed_at
12012025-04-03 10:00:00
22112025-05-07 12:30:00
32212025-05-31 23:59:00
42322025-04-10 09:00:00
52422025-04-30 23:30:00
62522025-05-01 00:10:00
72632025-05-12 08:00:00
82732025-05-13 08:00:00
92842025-03-31 22:00:00
102842025-04-15 11:00:00
112942025-05-15 11:00:00
122942025-05-16 11:00:00
132942025-06-01 00:00:00

Output

member_idapril_viewsmay_views
112
412

How to solve LinkedIn: Members Whose Profile Views Doubled in May

Both counts come from one grouped scan with conditional aggregation: each view adds 1 to the April column if it falls in April and to the May column if it falls in May, and views in other months add to neither. Because viewed_at is a datetime, the month is tested as a half-open range — >= '2025-05-01' AND < '2025-06-01' — which keeps a view at 23:59 on the 31st that a BETWEEN '2025-05-01' AND '2025-05-31' would lose (that upper bound is midnight at the start of the 31st).

The comparison runs in HAVING, on the grouped values. Requiring at least one April view matters: without it, a member with no views in either month would satisfy 0 ≥ 2 × 0, and a member who was first noticed in May has no April baseline to double. "At least double" is inclusive, so 2 → 4 qualifies.

The alternative builds a per-month count in two derived tables, using DATE_FORMAT(viewed_at, '%Y-%m') to pick the month, and LEFT JOINs May onto April so a member with no May views gets 0 rather than disappearing; starting from April enforces the baseline rule. It reads the table twice, the conditional version once.

Reference solution (MySQL)

SELECT viewed_id AS member_id,
       SUM(CASE WHEN viewed_at >= '2025-04-01' AND viewed_at < '2025-05-01' THEN 1 ELSE 0 END) AS april_views,
       SUM(CASE WHEN viewed_at >= '2025-05-01' AND viewed_at < '2025-06-01' THEN 1 ELSE 0 END) AS may_views
FROM ProfileView
GROUP BY viewed_id
HAVING april_views >= 1 AND may_views >= 2 * april_views
ORDER BY member_id

Another way

SELECT a.viewed_id AS member_id, a.views AS april_views, COALESCE(m.views, 0) AS may_views
FROM (SELECT viewed_id, COUNT(*) AS views FROM ProfileView WHERE DATE_FORMAT(viewed_at, '%Y-%m') = '2025-04' GROUP BY viewed_id) a
LEFT JOIN (SELECT viewed_id, COUNT(*) AS views FROM ProfileView WHERE DATE_FORMAT(viewed_at, '%Y-%m') = '2025-05' GROUP BY viewed_id) m
  ON m.viewed_id = a.viewed_id
WHERE COALESCE(m.views, 0) >= 2 * a.views
ORDER BY member_id

← LinkedIn: Invitation Acceptance With and Without a Note · LinkedIn: Most Common Reaction on Each Post →