<p>Consider the <code>events</code> table which contains information about the phases of writing a new social media post.</p>

<p>The <code>action</code> column can have values <code>post_enter</code>, <code>post_submit</code>, or <code>post_canceled</code> for when a user starts to write (<code>post_enter</code>), ends up canceling their post (<code>post_cancel</code>), or posts it (<code>post_submit</code>).</p>

<p>Write a query to get the post success rate for each day in the month of January 2020.</p>

<p>You can assume that a single user may only make one post per day.</p>

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

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

<p><code>events</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>action</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>url</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>platform</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Sample:</strong></p>

<table>
<thead>
<tr>
<th><code>user_id</code></th>
<th><code>created_at</code></th>
<th><code>event_name</code></th>
</tr>
</thead>

<tbody>
<tr>
<td>123</td>
<td>2019-01-01</td>
<td><code>post_enter</code></td>
</tr>

<tr>
<td>123</td>
<td>2019-01-01</td>
<td><code>post_submit</code></td>
</tr>

<tr>
<td>456</td>
<td>2019-01-02</td>
<td><code>post_enter</code></td>
</tr>

<tr>
<td>456</td>
<td>2019-01-02</td>
<td><code>post_cancel</code></td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>dt</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>post_success_rate</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>


Write a query to find the percentage of posts that ended up actually being published on the social media website

Post Success

Comments