analyze-subscription-conversion

java

python

python3

mysql

mssql

oraclesql

csharp

javascript

typescript

bash

swift

kotlin

dart

golang

ruby

scala

rust

racket

erlang

elixir

pythondata

postgresql

cangjie

Database

Table: <code>UserActivity</code>

<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>

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

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

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

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

UserActivity table:

<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>

Output:

<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>

Explanation:

<ul>
	<li>User 1:

	<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>User 2:
	<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>User 3:
	<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>User 4:
	<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>

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.
</div>

analyze_subscription_conversion

Analyze Subscription Conversion 

表：<code>UserActivity</code>

<pre>
+------------------+---------+
| Column Name | Type | 
+------------------+---------+
| user_id | int |
| activity_date | date |
| activity_type | varchar |
| activity_duration| int |
+------------------+---------+
(user_id, activity_date, activity_type) 是这张表的唯一主键。
activity_type 是('free_trial', 'paid', 'cancelled')中的一个。
activity_duration 是用户当天在平台上花费的分钟数。
每一行表示一个用户在特定日期的活动。
</pre>

订阅服务想要分析用户行为模式。公司提供7天免费试用，试用结束后，用户可以选择订阅 付费计划 或 取消。编写解决方案：

<ol>
	<li>查找从免费试用转为付费订阅的用户</li>
	<li>计算每位用户在 免费试用 期间的 平均每日活动时长（四舍五入至小数点后 <code>2</code> 位）</li>
	<li>计算每位用户在 付费 订阅期间的 平均每日活动时长（四舍五入到小数点后&nbsp;<code>2</code> 位）</li>
</ol>

返回结果表以&nbsp;<code>user_id</code> 升序&nbsp;排序。

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

UserActivity 表：

<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>

输出：

<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>

解释：

<ul>
	<li>用户 1:

	<ul>
		<li>体验了 3 天免费试用，时长分别为 45，30 和 60 分钟。</li>
		<li>平均试用时长：(45 + 30 + 60) / 3 = 45.00 分钟。</li>
		<li>拥有 3 天付费订阅，时长分别为 75，90 和 65分钟。</li>
		<li>平均花费市场：(75 + 90 + 65) / 3 = 76.67 分钟。</li>
	</ul>
	</li>
	<li>用户 2:
	<ul>
		<li>体验了 3 天免费试用，时长分别为 55，25 和 50 分钟。</li>
		<li>平均试用时长：(55 + 25 + 50) / 3 = 43.33 分钟。</li>
		<li>没有转为付费订阅（只有&nbsp;free_trial 和 cancelled 活动）。</li>
		<li>未包含在输出中，因为他未转换为付费用户。</li>
	</ul>
	</li>
	<li>用户 3:
	<ul>
		<li>体验了 3 天免费试用，时长分别为 70，60 和 80 分钟。</li>
		<li>平均试用时长：(70 + 60 + 80) / 3 = 70.00 分钟。</li>
		<li>拥有 3 天付费订阅，时长分别为 50，55 和 85 分钟。</li>
		<li>平均花费时长：(50 + 55 + 85) / 3 = 63.33 分钟。</li>
	</ul>
	</li>
	<li>用户 4:
	<ul>
		<li>体验了 2 天免费试用，时长分别为 40 和 35 分钟。</li>
		<li>平均试用时长：(40 + 35) / 2 = 37.50 分钟。</li>
		<li>在取消前有 1 天的付费订阅，时长为45分钟。</li>
		<li>平均花费时长：45.00 分钟。</li>
	</ul>
	</li>
</ul>

结果表仅包括从免费试用转为付费订阅的用户（用户 1，3 和 4），并且以&nbsp;user_id 升序排序。
</div>