Analyze Subscription Conversion 
<p>Table: <code>UserActivity</code></p>

<pre>
+------------------+---------+
| Column Name      | Type    | 
+------------------+---------+
| user_id          | int     |
| activity_date    | date    |
| activity_type    | varchar |
| activity_duration| int     |
+------------------+---------+
(user_id, activity_date, activity_type) is the unique key for this table.
activity_type is one of (&#39;free_trial&#39;, &#39;paid&#39;, &#39;cancelled&#39;).
activity_duration is the number of minutes the user spent on the platform that day.
Each row represents a user&#39;s activity on a specific date.
</pre>

<p>A subscription service wants to analyze user behavior patterns. The company offers a <code>7</code>-day <strong>free trial</strong>, after which users can subscribe to a <strong>paid plan</strong> or <strong>cancel</strong>. Write a solution to:</p>

<ol>
	<li>Find users who converted from free trial to paid subscription</li>
	<li>Calculate each user&#39;s <strong>average daily activity duration</strong> during their <strong>free trial</strong> period (rounded to <code>2</code> decimal places)</li>
	<li>Calculate each user&#39;s <strong>average daily activity duration</strong> during their <strong>paid</strong> subscription period (rounded to <code>2</code> decimal places)</li>
</ol>

<p>Return <em>the result table ordered by </em><code>user_id</code><em> in <strong>ascending</strong> order</em>.</p>

<p>The result format is in the following example.</p>

<p>&nbsp;</p>
<p><strong class="example">Example:</strong></p>

<div class="example-block">
<p><strong>Input:</strong></p>

<p>UserActivity table:</p>

<pre class="example-io">
+---------+---------------+---------------+-------------------+
| user_id | activity_date | activity_type | activity_duration |
+---------+---------------+---------------+-------------------+
| 1       | 2023-01-01    | free_trial    | 45                |
| 1       | 2023-01-02    | free_trial    | 30                |
| 1       | 2023-01-05    | free_trial    | 60                |
| 1       | 2023-01-10    | paid          | 75                |
| 1       | 2023-01-12    | paid          | 90                |
| 1       | 2023-01-15    | paid          | 65                |
| 2       | 2023-02-01    | free_trial    | 55                |
| 2       | 2023-02-03    | free_trial    | 25                |
| 2       | 2023-02-07    | free_trial    | 50                |
| 2       | 2023-02-10    | cancelled     | 0                 |
| 3       | 2023-03-05    | free_trial    | 70                |
| 3       | 2023-03-06    | free_trial    | 60                |
| 3       | 2023-03-08    | free_trial    | 80                |
| 3       | 2023-03-12    | paid          | 50                |
| 3       | 2023-03-15    | paid          | 55                |
| 3       | 2023-03-20    | paid          | 85                |
| 4       | 2023-04-01    | free_trial    | 40                |
| 4       | 2023-04-03    | free_trial    | 35                |
| 4       | 2023-04-05    | paid          | 45                |
| 4       | 2023-04-07    | cancelled     | 0                 |
+---------+---------------+---------------+-------------------+
</pre>

<p><strong>Output:</strong></p>

<pre class="example-io">
+---------+--------------------+-------------------+
| user_id | trial_avg_duration | paid_avg_duration |
+---------+--------------------+-------------------+
| 1       | 45.00              | 76.67             |
| 3       | 70.00              | 63.33             |
| 4       | 37.50              | 45.00             |
+---------+--------------------+-------------------+
</pre>

<p><strong>Explanation:</strong></p>

<ul>
	<li><strong>User 1:</strong>

	<ul>
		<li>Had 3 days of free trial with durations of 45, 30, and 60 minutes.</li>
		<li>Average trial duration: (45 + 30 + 60) / 3 = 45.00 minutes.</li>
		<li>Had 3 days of paid subscription with durations of 75, 90, and 65 minutes.</li>
		<li>Average paid duration: (75 + 90 + 65) / 3 = 76.67 minutes.</li>
	</ul>
	</li>
	<li><strong>User 2:</strong>
	<ul>
		<li>Had 3 days of free trial with durations of 55, 25, and 50 minutes.</li>
		<li>Average trial duration: (55 + 25 + 50) / 3 = 43.33 minutes.</li>
		<li>Did not convert to a paid subscription (only had free_trial and cancelled activities).</li>
		<li>Not included in the output because they didn&#39;t convert to paid.</li>
	</ul>
	</li>
	<li><strong>User 3:</strong>
	<ul>
		<li>Had 3 days of free trial with durations of 70, 60, and 80 minutes.</li>
		<li>Average trial duration: (70 + 60 + 80) / 3 = 70.00 minutes.</li>
		<li>Had 3 days of paid subscription with durations of 50, 55, and 85 minutes.</li>
		<li>Average paid duration: (50 + 55 + 85) / 3 = 63.33 minutes.</li>
	</ul>
	</li>
	<li><strong>User 4:</strong>
	<ul>
		<li>Had 2 days of free trial with durations of 40 and 35 minutes.</li>
		<li>Average trial duration: (40 + 35) / 2 = 37.50 minutes.</li>
		<li>Had 1 day of paid subscription with duration of 45 minutes before cancelling.</li>
		<li>Average paid duration: 45.00 minutes.</li>
	</ul>
	</li>
</ul>

<p>The result table only includes users who converted from free trial to paid subscription (users 1, 3, and 4), and is ordered by user_id in ascending order.</p>
</div>

[]
['CREATE TABLE if not exists UserActivity (\n    user_id INT,\n    activity_date DATE,\n    activity_type VARCHAR(20),\n    activity_duration INT\n)', 'Truncate table UserActivity', "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('1', '2023-01-01', 'free_trial', '45')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('1', '2023-01-02', 'free_trial', '30')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('1', '2023-01-05', 'free_trial', '60')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('1', '2023-01-10', 'paid', '75')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('1', '2023-01-12', 'paid', '90')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('1', '2023-01-15', 'paid', '65')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('2', '2023-02-01', 'free_trial', '55')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('2', '2023-02-03', 'free_trial', '25')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('2', '2023-02-07', 'free_trial', '50')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('2', '2023-02-10', 'cancelled', '0')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('3', '2023-03-05', 'free_trial', '70')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('3', '2023-03-06', 'free_trial', '60')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('3', '2023-03-08', 'free_trial', '80')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('3', '2023-03-12', 'paid', '50')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('3', '2023-03-15', 'paid', '55')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('3', '2023-03-20', 'paid', '85')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('4', '2023-04-01', 'free_trial', '40')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('4', '2023-04-03', 'free_trial', '35')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('4', '2023-04-05', 'paid', '45')", "insert into UserActivity (user_id, activity_date, activity_type, activity_duration) values ('4', '2023-04-07', 'cancelled', '0')"]
[{"value": "mysql", "text": "MySQL", "defaultCode": "# Write your MySQL query statement below\n"}, {"value": "mssql", "text": "MS SQL Server", "defaultCode": "/* Write your T-SQL query statement below */\n"}, {"value": "oraclesql", "text": "Oracle", "defaultCode": "/* Write your PL/SQL query statement below */\n"}, {"value": "pythondata", "text": "Pandas", "defaultCode": "import pandas as pd\n\ndef analyze_subscription_conversion(user_activity: pd.DataFrame) -> pd.DataFrame:\n    "}, {"value": "postgresql", "text": "PostgreSQL", "defaultCode": "-- Write your PostgreSQL query statement below\n"}]
{"mysql": ["CREATE TABLE if not exists UserActivity (\n    user_id INT,\n    activity_date DATE,\n    activity_type VARCHAR(20),\n    activity_duration INT\n)"], "mssql": ["CREATE TABLE UserActivity (\n    user_id INT,\n    activity_date DATE,\n    activity_type VARCHAR(20),\n    activity_duration INT\n)"], "oraclesql": ["CREATE TABLE UserActivity (\n    user_id NUMBER,\n    activity_date DATE,\n    activity_type VARCHAR2(20),\n    activity_duration NUMBER\n)", "ALTER SESSION SET nls_date_format='YYYY-MM-DD'"], "database": true, "name": "analyze_subscription_conversion", "postgresql": ["CREATE TABLE IF NOT EXISTS UserActivity (\n    user_id INTEGER,\n    activity_date DATE,\n    activity_type VARCHAR(20),\n    activity_duration INTEGER\n);\n"], "pythondata": ["UserActivity = pd.DataFrame({\n    'user_id': pd.Series(dtype='int'),\n    'activity_date': pd.Series(dtype='datetime64[ns]'),\n    'activity_type': pd.Series(dtype='str'),\n    'activity_duration': pd.Series(dtype='int')\n})"], "database_schema": {"UserActivity": {"user_id": "INT", "activity_date": "DATE", "activity_type": "VARCHAR(20)", "activity_duration": "INT"}}}
{"mysql": ["MySQL", "<p><code>MySQL 8.0</code>.</p>"], "mssql": ["MS SQL Server", "<p><code>mssql server 2019</code>.</p>"], "oraclesql": ["Oracle", "<p><code>Oracle Sql 11.2</code>.</p>"], "pythondata": ["Pandas", "<p>Python 3.10 with Pandas 2.2.2 and NumPy 1.26.4</p>"], "postgresql": ["PostgreSQL", "<p>PostgreSQL 16</p>"]}
['Database']