Find the first job posted by each company.
Table Schema
| Column | Type | Description |
|---|---|---|
| job_id | integer | Unique job posting identifier. |
| company_id | integer | Identifier for the company posting the job. |
| title | string | Job listing title. |
| posted_date | date | Date the job was posted. |
| location | string | Job location. |
| salary | integer | Listed salary. |
| recruiter_id | integer | Recruiter identifier. |
Sample Data
| job_id | company_id | title | posted_date | location | salary | recruiter_id |
|---|---|---|---|---|---|---|
| 1 | 1 | Analyst | 2024-01-01 | NY | 60000 | 11 |
| 2 | 1 | Engineer | 2024-02-01 | NY | 90000 | 12 |
| 3 | 2 | Scientist | 2024-01-10 | SF | 120000 | 13 |
| 4 | 2 | Manager | 2024-01-20 | SF | 110000 | 14 |
| 5 | 3 | Analyst | 2024-01-05 | TX | 70000 | 15 |
| 6 | 3 | Engineer | 2024-02-10 | TX | 95000 | 16 |
| company_id | job_id | posted_date |
|---|---|---|
| 1 | 1 | 2024-01-01 |
| 2 | 3 | 2024-01-10 |
| 3 | 5 | 2024-01-05 |
SQL Editor
| company_id | job_id | posted_date |
|---|---|---|
| 1 | 1 | 2024-01-01 |
| 2 | 3 | 2024-01-10 |
| 3 | 5 | 2024-01-05 |
Hints
Solution
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
WITH ranked_jobs AS (
SELECT
company_id,
job_id,
posted_date,
ROW_NUMBER() OVER (
PARTITION BY company_id
ORDER BY posted_date
) AS rn
FROM job_listings
)
SELECT
company_id,
job_id,
posted_date
FROM ranked_jobs
WHERE rn = 1;
Explanation
Rank each company's jobs by posted_date using ROW_NUMBER().
Give the earliest job rank 1 inside every company partition.
Select the company_id, job_id, and posted_date where rn equals 1.
Related Questions