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

<p>Table <strong><code>job_postings</code></strong></p>

<table>
<thead>
<tr>
<th>column</th>
<th>type</th>
</tr>
</thead>

<tbody>
<tr>
<td>job_posting_id</td>
<td>integer</td>
</tr>

<tr>
<td>job_id</td>
<td>integer</td>
</tr>

<tr>
<td>user_id</td>
<td>integer</td>
</tr>

<tr>
<td>date_posted</td>
<td>datetime</td>
</tr>

<tr>
<td>is_revoked</td>
<td>boolean</td>
</tr>
</tbody>
</table>
<p>Create a query that returns a percentage of revoked jobs from the last 180 days, starting from the current date.</p>

<p><strong>Notes:</strong></p>

<ul>
<li><p><em>The current date can be inferred by querying for the most recent date entry on the table.</em></p></li>

<li><p><em>When a <code>job_id</code> is associated with two or more <code>job_posting_ID</code>-s, use the most recent job posting.
The returned percentage should truncate all the values beyond two decimal points.</em></p></li>
</ul>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<table>
<thead>
<tr>
<th>job_posting_id</th>
<th>job_id</th>
<th>user_id</th>
<th>date_posted</th>
<th>is_revoked</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>1</td>
<td>1</td>
<td>2023-02-02</td>
<td>false</td>
</tr>

<tr>
<td>2</td>
<td>2</td>
<td>2</td>
<td>2024-01-05</td>
<td>true</td>
</tr>

<tr>
<td>3</td>
<td>3</td>
<td>3</td>
<td>2024-01-22</td>
<td>false</td>
</tr>

<tr>
<td>4</td>
<td>3</td>
<td>4</td>
<td>2024-02-04</td>
<td>true</td>
</tr>

<tr>
<td>5</td>
<td>4</td>
<td>5</td>
<td>2024-02-05</td>
<td>false</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>percentage</th>
</tr>
</thead>

<tbody>
<tr>
<td>33.33</td>
</tr>
</tbody>
</table>


Find the percentage of users that posted a job more than 180 days ago

180 Day Job Postings

Comments