Interview Query

180 Day Job Postings

Start Timer

0:00:00

Upvote
5
Downvote
Save question
Mark as completed
View comments (22)
Next question

Let’s say you work for a job board website. You have the following table representing a list of job postings.

Table job_postings

column type
job_posting_id integer
job_id integer
user_id integer
date_posted datetime
is_revoked boolean

Create a query that returns a percentage of revoked jobs from the last 180 days, starting from the current date.

Notes:

  • The current date can be inferred by querying for the most recent date entry on the table.

  • When a job_id is associated with two or more job_posting_ID-s, use the most recent job posting. The returned percentage should truncate all the values beyond two decimal points.

Example:

Input:

job_posting_id job_id user_id date_posted is_revoked
1 1 1 2023-02-02 false
2 2 2 2024-01-05 true
3 3 3 2024-01-22 false
4 3 4 2024-02-04 true
5 4 5 2024-02-05 false

Output:

percentage
33.33
.
.
.
.
.


Comments

Loading comments