Interview Query

180 Day Job Postings

0
Have you seen this question before?

Let’s say you work at a job board website. It is currently January 1st, 2022. You have the following table containing information about employers who have posted a job to the website.

job_postings table

column type
job_id integer
user_id integer
date_posted datetime

Write a query to return the percentage of users that have either

  1. posted a job that is more than 180 days ago.
  2. posted a job with the same job_id as a previous job posting(s), that altogether, have been on the website for more than 180 days.

Call the output’s single column percentage

Example

Input

job_id user_id date_posted
1 1 03-21-2021
2 2 10-21-2021
3 3 02-06-2021
3 3 10-20-2021

Output

percentage
0.3000
Next question: Permanent Deletion Change
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query